package com.dhcc.DRGsHosp.application.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.inject.Named;

import org.dayatang.querychannel.JdbcTemplateObj;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.transaction.annotation.Transactional;

import com.dhcc.DRGsHosp.application.DisGroupAnalysisApplication;
import com.dhcc.DRGsHosp.core.domain.DrgsAnalysis;
import com.dhcc.DRGsHosp.facade.DrgsIndexCalFacade;
import com.dhcc.DRGsHosp.facade.dto.IndexCalDTO;
import com.dhcc.DRGsHosp.infra.util.DrgsIndex;
import com.dhcc.DRGsHosp.infra.util.StringUtils;


/**
 * @title		:DrgsAnalysisApplicationImpl.java
 * @description	:
 * @author		:WangTeng
 * @date		:2015年5月12日
 * @version     :1.0
 * @copyright   :DHCC 2013-2014 
 */
@Named
@Transactional
public class DisGroupAnalysisApplicationImpl implements DisGroupAnalysisApplication {
	
	@Autowired
	private JdbcTemplateObj jdbcTemplateObj;
	
	@Autowired
	private DrgsIndexCalFacade drgsIndexCalFacade;
    
    /**
	 * 
	 * @methodname    :twoYearsCompare
	 * @description   : 两年比较
	 * @param         :
	 * @author        :mazhaoyan
	 * @return        :
	 * @date          :2015年12月12日
	 */
    @Override
    @Cacheable(value = "drgsCaseDetail3.1.1", key = "'twoYearsCompare' + #drgsAnalysis.wardCode + #drgsAnalysis.depCode + #drgsAnalysis.depGroupCode + #drgsAnalysis.hospCode "
    					+" + #drgsAnalysis.stdId + #drgsAnalysis.verRegion + #drgsAnalysis.dateFrom + #drgsAnalysis.dateTo ")
	public Map<String, Object> twoYearsCompare(DrgsAnalysis drgsAnalysis) {
    	String wardCode = drgsAnalysis.getWardCode();
		String depCode = drgsAnalysis.getDepCode();
		String depGroupCode = drgsAnalysis.getDepGroupCode();
		String hospCode = drgsAnalysis.getHospCode();
    	String dateFrom= DrgsIndex.formateDateFrom(drgsAnalysis.getDateFrom());
		String dateTo=DrgsIndex.formateDateTo(drgsAnalysis.getDateTo());	
		String stdId=drgsAnalysis.getStdId();//标杆的id
		String groupScheme = drgsAnalysis.getVerRegion();
    	String condition = "";//条件字符串
    	Map<String,Object> result = new HashMap<String,Object>();

    	//获取标杆年份
		String sqlYear = "select distinct as_year from DRGs.t_annualstd t where t.as_id='"+stdId+"'";
		Integer stdYear = jdbcTemplateObj.queryForObject(sqlYear, Integer.class);
		result.put("year",stdYear);
    			
		//科室组
		if (StringUtils.hasLength(depGroupCode)&&!"null".equals(depGroupCode)&&!"hosp".equals(depGroupCode)) {
			condition += "  and dep.pardepgroup_code = '"+depGroupCode+"'";
		} 
		//科室
		if (StringUtils.hasLength(depCode)&&!"null".equals(depCode)) {
			if(depCode.indexOf(",")>-1)
			{//多个科室
				depCode = "'"+depCode.replaceAll(",","','")+"'";
				condition += "  and dep.dep_code in ("+depCode+")";
			}
			else
			{//一个科室
				condition += "  and dep.dep_code = '"+depCode+"'";
			}
		}
		//病区
		if (StringUtils.hasLength(wardCode)&&!"null".equals(wardCode)) {
			condition += "  and a.P00310001 = '"+wardCode+"'";
		}
		StringBuilder sql  = new StringBuilder();
		//全市均值  本期病例
		sql.setLength(0);
		sql.append("  select count(*) drgcnt, sum(pcnt) casecnt,sum(pcnt*as_avg_fee) totalfee,sum(pcnt*as_avg_fee)/sum(pcnt) avgfee from ( \n ");
		sql.append("  select b.pardrg_code,count(*) pcnt \n ");
		sql.append("  from drgs.mr_baseinfo bs left join drgs.mr_ipinfo a on bs.pmainindex=a.pmainindex   \n ");
		sql.append("  left join DRGs.t_patientdrg b    \n ");
		sql.append("  on a.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"'  \n ");
		sql.append("  left join DRGs.t_department dep   \n ");
		sql.append("  on a.P00300001=dep.dep_code   \n ");
		sql.append("  where to_char(a.P00290000, 'yyyy-mm-dd')   between '"+dateFrom+"' and '"+dateTo+"' \n ");
  		sql.append("  and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append( condition );
		sql.append("  and b.pardrg_code is not null \n ");
		sql.append("  group by b.pardrg_code) a left join  \n ");
		sql.append("  drgs.t_annualstd std  \n ");
		sql.append("  on a.pardrg_code = std.as_drg_code \n ");
		sql.append("  and std.as_id = '"+stdId+"' \n ");
		List<Map<String,Object>> thisPeriodCity = jdbcTemplateObj.queryForList(sql.toString()) ;
		result.put("thisPeriodCity",thisPeriodCity);
		//全市均值  同期病例
		sql.setLength(0);
		sql.append("  select count(*) drgcnt, sum(pcnt) casecnt,sum(pcnt*as_avg_fee) totalfee,sum(pcnt*as_avg_fee)/sum(pcnt) avgfee from ( \n ");
		sql.append("  select b.pardrg_code,count(*) pcnt \n ");
		sql.append("  from drgs.mr_baseinfo bs left join drgs.mr_ipinfo a on bs.pmainindex=a.pmainindex   \n ");
		sql.append("  left join DRGs.t_patientdrg b    \n ");
		sql.append("  on a.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"' \n ");
		sql.append("  left join DRGs.t_department dep   \n ");
		sql.append("  on a.P00300001=dep.dep_code   \n ");
		sql.append("  where to_char(a.P00290000, 'yyyy-mm-dd')   between '"+DrgsIndex.getDateFromCc(dateFrom,"year")+"' and '"+DrgsIndex.getDateFromCc(dateTo,"year")+"' \n ");
  		sql.append("  and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append( condition );
		sql.append("  and b.pardrg_code is not null \n ");
		sql.append("  group by b.pardrg_code) a left join  \n ");
		sql.append("  drgs.t_annualstd std  \n ");
		sql.append("  on a.pardrg_code = std.as_drg_code \n ");
		sql.append("  and std.as_id = '"+stdId+"'  \n ");
		List<Map<String,Object>>  lastPeriodCity = jdbcTemplateObj.queryForList(sql.toString()) ;
		result.put("lastPeriodCity",lastPeriodCity);
		
		//下面4个sql是原始的6行表格中的4行数据
		//本年均值 本期病例
		sql.setLength(0);
		sql.append("   select count(*) drgcnt, sum(pcnt) casecnt,sum(pcnt*drgavgfee) totalfee,sum(pcnt*drgavgfee)/sum(pcnt) avgfee from ( \n ");
		sql.append("   select b.pardrg_code,count(*) pcnt \n ");
		sql.append("   from drgs.mr_baseinfo bs left join drgs.mr_ipinfo a on bs.pmainindex=a.pmainindex   \n ");
		sql.append("   left join DRGs.t_patientdrg b    \n ");
		sql.append("   on a.pmainindex=b.parid  and b.ver_region='"+groupScheme+"' \n ");
		sql.append("   left join DRGs.t_department dep   \n ");
		sql.append("   on a.P00300001=dep.dep_code    \n ");
		sql.append("   where to_char(a.P00290000, 'yyyy-mm-dd')   between '"+dateFrom+"' and '"+dateTo+"' \n ");
  		sql.append("  and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append( condition );
		sql.append("   and b.pardrg_code is not null  \n ");
		sql.append("   group by b.pardrg_code) a  \n ");
		sql.append("   left join  \n ");
		sql.append("   (select b.pardrg_code,avg(a.P00800000) drgavgfee \n ");
		sql.append("   from DRGs.mr_ipinfo a   \n ");
		sql.append("   left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
		sql.append("   left join DRGs.t_patientdrg b    \n ");
		sql.append("   on a.pmainindex=b.parid   \n ");
		sql.append("   left join DRGs.t_department dep   \n ");
		sql.append("   on a.P00300001=dep.dep_code    \n "); 
		//sql.append("   where to_char(a.P00290000, 'yyyy')='"+thisYear+"' \n ");
		sql.append("   where to_char(a.P00290000, 'yyyy-mm-dd')   between '"+dateFrom+"' and '"+dateTo+"' \n ");
  		sql.append("  and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append( condition );
		sql.append("   and b.pardrg_code is not null  \n ");
		sql.append("   group by b.pardrg_code) b \n ");
		sql.append("   on a.pardrg_code = b.pardrg_code  \n ");
		List<Map<String,Object>>  thisPeriodThisYear = jdbcTemplateObj.queryForList(sql.toString()) ;
		result.put("thisPeriodThisYear",thisPeriodThisYear);
		
		//本年均值 同期病例
		sql.setLength(0);
		sql.append("   select count(*) drgcnt, sum(pcnt) casecnt,sum(pcnt*drgavgfee) totalfee,sum(pcnt*drgavgfee)/sum(pcnt) avgfee from ( \n ");
		sql.append("   select b.pardrg_code,count(*) pcnt \n ");
		sql.append("   from drgs.mr_baseinfo bs left join drgs.mr_ipinfo a on bs.pmainindex=a.pmainindex   \n ");
		sql.append("   left join DRGs.t_patientdrg b    \n ");
		sql.append("   on a.pmainindex=b.parid  and b.ver_region='"+groupScheme+"'  \n ");
		sql.append("   left join DRGs.t_department dep   \n ");
		sql.append("   on a.P00300001=dep.dep_code    \n ");
		sql.append("   where to_char(a.P00290000, 'yyyy-mm-dd')   between '"+DrgsIndex.getDateFromCc(dateFrom,"year")+"' and '"+DrgsIndex.getDateFromCc(dateTo,"year")+"' \n ");
  		sql.append("  and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append( condition );
		sql.append("   and b.pardrg_code is not null  \n ");
		sql.append("   group by b.pardrg_code) a  \n ");
		sql.append("   left join  \n ");
		sql.append("   (select b.pardrg_code,avg(a.P00800000) drgavgfee \n ");
		sql.append("   from DRGs.mr_ipinfo a   \n ");
		sql.append("   left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
		sql.append("   left join DRGs.t_patientdrg b    \n ");
		sql.append("   on a.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"' \n ");
		sql.append("   left join DRGs.t_department dep   \n ");
		sql.append("   on a.P00300001=dep.dep_code    \n ");
		//sql.append("   where to_char(a.P00290000, 'yyyy')='"+thisYear+"' \n ");
		sql.append("   where to_char(a.P00290000, 'yyyy-mm-dd')   between '"+DrgsIndex.getDateFromCc(dateFrom,"year")+"' and '"+DrgsIndex.getDateFromCc(dateTo,"year")+"' \n ");
  		sql.append("  and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append( condition );
		sql.append("   and b.pardrg_code is not null  \n ");
		sql.append("   group by b.pardrg_code) b \n ");
		sql.append("   on a.pardrg_code = b.pardrg_code  \n ");
		List<Map<String,Object>>  lastPeriodThisYear = jdbcTemplateObj.queryForList(sql.toString()) ;
		result.put("lastPeriodThisYear",lastPeriodThisYear);
		//上年均值 本期病例
		sql.setLength(0);
		sql.append("   select count(*) drgcnt, sum(pcnt) casecnt,sum(pcnt*drgavgfee) totalfee,sum(pcnt*drgavgfee)/sum(pcnt) avgfee from ( \n ");
		sql.append("   select b.pardrg_code,count(*) pcnt \n ");
		sql.append("   from drgs.mr_baseinfo bs left join drgs.mr_ipinfo a on bs.pmainindex=a.pmainindex   \n ");
		sql.append("   left join DRGs.t_patientdrg b    \n ");
		sql.append("   on a.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"' \n ");
		sql.append("   left join DRGs.t_department dep   \n ");
		sql.append("   on a.P00300001=dep.dep_code    \n ");
		sql.append("   where to_char(a.P00290000, 'yyyy-mm-dd')   between '"+dateFrom+"' and '"+dateTo+"' \n ");
		sql.append("  and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append( condition );
		sql.append("   and b.pardrg_code is not null  \n ");
		sql.append("   group by b.pardrg_code) a  \n ");
		sql.append("   left join  \n ");
		sql.append("   (select b.pardrg_code,avg(a.P00800000) drgavgfee \n ");
		sql.append("   from DRGs.mr_ipinfo a  left join drgs.mr_baseinfo bs on bs.pmainindex=a.pmainindex \n ");
		sql.append("   left join DRGs.t_patientdrg b    \n ");
		sql.append("   on a.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"' \n ");
		sql.append("   left join DRGs.t_department dep   \n ");
		sql.append("   on a.P00300001=dep.dep_code    \n ");
		sql.append("   where to_char(a.P00290000, 'yyyy-mm-dd')  between '"+dateFrom+"' and '"+dateTo+"'\n ");
		sql.append("  and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append( condition );
		sql.append("   and b.pardrg_code is not null  \n ");
		sql.append("   group by b.pardrg_code) b \n ");
		sql.append("   on a.pardrg_code = b.pardrg_code  \n ");
		List<Map<String,Object>>  thisPeriodLastYear = jdbcTemplateObj.queryForList(sql.toString()) ;
		result.put("thisPeriodLastYear",thisPeriodLastYear);
		//上年均值 同期病例
		sql.setLength(0);
		sql.append("   select count(*) drgcnt, sum(pcnt) casecnt,sum(pcnt*drgavgfee) totalfee,sum(pcnt*drgavgfee)/sum(pcnt) avgfee from ( \n ");
		sql.append("   select b.pardrg_code,count(*) pcnt \n ");
		sql.append("   from drgs.mr_baseinfo bs left join drgs.mr_ipinfo a on bs.pmainindex=a.pmainindex   \n ");
		sql.append("   left join DRGs.t_patientdrg b    \n ");
		sql.append("   on a.pmainindex=b.parid  and b.ver_region='"+groupScheme+"' \n ");
		sql.append("   left join DRGs.t_department dep   \n ");
		sql.append("   on a.P00300001=dep.dep_code    \n ");
		sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append("   where to_char(a.P00290000, 'yyyy-mm-dd')   between '"+DrgsIndex.getDateFromCc(dateFrom,"year")+"' and '"+DrgsIndex.getDateFromCc(dateTo,"year")+"' \n ");
		sql.append( condition );
		sql.append("   and b.pardrg_code is not null  \n ");
		sql.append("   group by b.pardrg_code) a  \n ");
		sql.append("   left join  \n ");
		sql.append("   (select b.pardrg_code,avg(a.P00800000) drgavgfee \n ");
		sql.append("   from DRGs.mr_ipinfo a left join drgs.mr_baseinfo bs on bs.pmainindex=a.pmainindex   \n ");
		sql.append("   left join DRGs.t_patientdrg b    \n ");
		sql.append("   on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"'  \n ");
		sql.append("   left join DRGs.t_department dep   \n ");
		sql.append("   on a.P00300001=dep.dep_code    \n ");
		sql.append("   where to_char(a.P00290000, 'yyyy-mm-dd')  between '"+DrgsIndex.getDateFromCc(dateFrom,"year")+"' and '"+DrgsIndex.getDateFromCc(dateTo,"year")+"' \n ");
		sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append( condition );
		sql.append("   and b.pardrg_code is not null  \n ");
		sql.append("   group by b.pardrg_code) b \n ");
		sql.append("   on a.pardrg_code = b.pardrg_code  \n ");
		List<Map<String,Object>>  lastPeriodLastYear = jdbcTemplateObj.queryForList(sql.toString()) ;
		result.put("lastPeriodLastYear",lastPeriodLastYear);
		
		return result;
	}
    
    /**
	 * 
	 * @methodname    :drgsStructure
	 * @description   : 病组结构分析
	 * @param         :
	 * @author        :mazhaoyan
	 * @return        :
	 * @date          :2015年12月14日
	 */
	@Override
	@Cacheable(value = "drgsCaseDetail3.1.1", key = "'drgsStructure' + #drgsAnalysis.wardCode + #drgsAnalysis.depCode + #drgsAnalysis.depGroupCode + #drgsAnalysis.hospCode "
					+" + #drgsAnalysis.stdId + #drgsAnalysis.verRegion + #drgsAnalysis.dateFrom + #drgsAnalysis.dateTo")
	public Map<String, Object> drgsStructure(DrgsAnalysis drgsAnalysis) {
		String wardCode = drgsAnalysis.getWardCode();
		String depCode = drgsAnalysis.getDepCode();
		String depGroupCode = drgsAnalysis.getDepGroupCode();
		String hospCode = drgsAnalysis.getHospCode();
    	String dateFrom= DrgsIndex.formateDateFrom(drgsAnalysis.getDateFrom());
		String dateTo=DrgsIndex.formateDateTo(drgsAnalysis.getDateTo());	
		String monthFrom=drgsAnalysis.getDateFrom().substring(5);	//起始月份
		String monthTo=drgsAnalysis.getDateTo().substring(5);	//起始月份
		String stdId=drgsAnalysis.getStdId();//标杆的id
		String groupScheme = drgsAnalysis.getVerRegion();
		int thisYear = Integer.parseInt(dateFrom.substring(0,4));//当年
    	String condition = "";//条件字符串
    	Map<String,Object> result = new HashMap<String,Object>();

    	dateFrom= DrgsIndex.formateDateFrom(dateFrom);
		dateTo=DrgsIndex.formateDateTo(dateTo);	
		
		//获取管理目标年份
		IndexCalDTO dto = new IndexCalDTO();
		dto.setDateTo(dateTo);
		dto.setMsCode("std_");
		dto.setpType("0");
		
		//科室组
		if (StringUtils.hasLength(depGroupCode)&&!"null".equals(depGroupCode)&&!"hosp".equals(depGroupCode)) {
			condition += "  and dep.pardepgroup_code = '"+depGroupCode+"'";
		} 
		//科室
		if (StringUtils.hasLength(depCode)&&!"null".equals(depCode)) {
			if(depCode.indexOf(",")>-1)
			{//多个科室
				depCode = "'"+depCode.replaceAll(",","','")+"'";
				condition += "  and dep.dep_code in ("+depCode+")";
			}
			else
			{//一个科室
				condition += "  and dep.dep_code = '"+depCode+"'";
			}
		}
		//病区
		if (StringUtils.hasLength(wardCode)&&!"null".equals(wardCode)) {
			condition += "  and a.P00310001 = '"+wardCode+"'";
		}
		StringBuilder sql  = new StringBuilder();
		//本期覆盖
		sql.setLength(0);
		sql.append("   select count(distinct a.pardrg_code) drgCnt,sum(incasecnt) incasecnt,sum(TotalFee) TotalFee,sum(TotalFee)/sum(incasecnt) avgfee,  \n ");
		sql.append("   case when  sum(InCaseCnt)<>0 then sum(TotalWeight)/sum(InCaseCnt) else 0 end cmi,  \n ");
		sql.append("   sum(TotalWeight) TotalWeight,  \n ");
		sql.append("   sum(case when nvl(avgfee,0)-nvl(a.as_avg_fee,0)>0 then 1 else 0 end ) as overfeecnt from (  \n ");
		sql.append("   select b.pardrg_code,  \n ");
		sql.append("   sum(case when b.pardrg_code is not null and a.P00320000 < 61 then  1 else 0 end) as InCaseCnt,  \n ");
		sql.append("   sum(a.P00800000)  as TotalFee,c.as_avg_fee,  \n ");
		sql.append("   avg(a.P00800000) avgfee,  \n ");
		sql.append("   sum(case when b.pardrg_code is null then 0 else c.as_weight end) as TotalWeight  \n ");
		sql.append("   from drgs.mr_baseinfo bs left join drgs.mr_ipinfo a on bs.pmainindex=a.pmainindex   \n ");
		sql.append("   left join DRGs.t_patientdrg b    \n ");
		sql.append("   on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"'  \n ");
		sql.append("   left join DRGs.t_drgversion dr \n " );
		sql.append("   on b.ver_code=dr.ver_code   \n ");	
		sql.append("   left join DRGs.t_department dep   \n ");
		sql.append("   on a.P00300001=dep.dep_code   \n ");//出院科别代码
		sql.append("   left join DRGs.t_annualstd c   \n ");
		sql.append("   on b.pardrg_code=c.as_drg_code and c.as_id = '"+stdId+"'\n ");
		sql.append("   where to_char(a.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
		sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append("   and b.pardrg_code is not null \n ");
		sql.append( condition );
		sql.append("   group by b.pardrg_code,c.as_avg_fee \n ");
		sql.append("   ) a  \n ");
		List<Map<String,Object>>  thisPeriod = jdbcTemplateObj.queryForList(sql.toString()) ;
		result.put("thisPeriod",thisPeriod);
		//同期覆盖
		sql.setLength(0);
		sql.append("   select count(distinct a.pardrg_code) drgCnt,sum(incasecnt) incasecnt,sum(TotalFee) TotalFee,sum(TotalFee)/sum(incasecnt) avgfee,  \n ");
		sql.append("   case when  sum(InCaseCnt)<>0 then sum(TotalWeight)/sum(InCaseCnt) else 0 end cmi,  \n ");
		sql.append("   sum(TotalWeight) TotalWeight,  \n ");
		sql.append("   sum(case when nvl(avgfee,0)-nvl(a.as_avg_fee,0)>0 then 1 else 0 end ) as overfeecnt from (  \n ");
		sql.append("   select b.pardrg_code,  \n ");
		sql.append("   sum(case when b.pardrg_code is not null and a.P00320000 < 61 then  1 else 0 end) as InCaseCnt,  \n ");
		sql.append("   sum(a.P00800000)  as TotalFee,c.as_avg_fee,  \n ");
		sql.append("   avg(a.P00800000) avgfee,  \n ");
		sql.append("   sum(case when b.pardrg_code is null then 0 else c.as_weight end) as TotalWeight  \n ");
		sql.append("   from drgs.mr_baseinfo bs left join drgs.mr_ipinfo a on bs.pmainindex=a.pmainindex   \n ");
		sql.append("   left join DRGs.t_patientdrg b   \n ");
		sql.append("   on a.pmainindex=b.parid   and  b.ver_region='"+groupScheme+"' \n ");
		sql.append("   left join DRGs.t_drgversion dr \n " );
		sql.append("   on b.ver_code=dr.ver_code   \n ");
		sql.append("   left join DRGs.t_department dep   \n ");
		sql.append("   on a.P00300001=dep.dep_code   \n ");
		sql.append("   left join DRGs.t_annualstd c   \n ");
		sql.append("   on b.pardrg_code=c.as_drg_code and c.as_id = '"+stdId+"'  \n ");
		sql.append("   where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"'  \n ");
		sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append("   and b.pardrg_code is not null \n ");
		sql.append( condition );
		sql.append("   group by b.pardrg_code,c.as_avg_fee \n ");
		sql.append("   ) a  \n ");
		List<Map<String,Object>>  lastPeriod = jdbcTemplateObj.queryForList(sql.toString()) ;
		result.put("lastPeriod",lastPeriod);
		//仅同期覆盖
		sql.setLength(0);
		sql.append("   select count(distinct a.pardrg_code) drgCnt,sum(incasecnt) incasecnt,sum(TotalFee) TotalFee,sum(TotalFee)/sum(incasecnt) avgfee,  \n ");
		sql.append("   case when  sum(InCaseCnt)<>0 then sum(TotalWeight)/sum(InCaseCnt) else 0 end cmi,  \n ");
		sql.append("   sum(TotalWeight) TotalWeight,  \n ");
		sql.append("   sum(case when nvl(avgfee,0)-nvl(a.as_avg_fee,0)>0 then 1 else 0 end ) as overfeecnt from (  \n ");
		sql.append("   select b.pardrg_code,  \n ");
		sql.append("   sum(case when b.pardrg_code is not null and a.P00320000 < 61 then  1 else 0 end) as InCaseCnt,  \n ");
		sql.append("   sum(a.P00800000)  as TotalFee,c.as_avg_fee,  \n ");
		sql.append("   avg(a.P00800000) avgfee,  \n ");
		sql.append("   sum(case when b.pardrg_code is null then 0 else c.as_weight end) as TotalWeight  \n ");
		sql.append("   from drgs.mr_baseinfo bs left join drgs.mr_ipinfo a on bs.pmainindex=a.pmainindex   \n ");
		sql.append("   left join DRGs.t_patientdrg b    \n ");
		sql.append("   on a.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"'  \n ");
		sql.append("   left join DRGs.t_drgversion dr \n" );
		sql.append("   on b.ver_code=dr.ver_code   \n ");
		sql.append("   left join DRGs.t_department dep   \n ");
		sql.append("   on a.P00300001=dep.dep_code   \n ");
		sql.append("   left join DRGs.t_annualstd c   \n ");
		sql.append("   on b.pardrg_code=c.as_drg_code and c.as_id = '"+stdId+"'  \n ");
		sql.append("   where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"'  \n ");
		sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append("   and b.pardrg_code is not null \n ");
		sql.append( condition );
		sql.append("   and b.pardrg_code  not in ( \n ");
		sql.append("   select distinct(b.pardrg_code) from DRGs.mr_ipinfo a   \n ");
		sql.append("   left join DRGs.mr_baseinfo bs on bs.pmainindex=a.pmainindex \n ");
		sql.append("   left join DRGs.t_patientdrg b \n ");   
		sql.append("   on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"' \n ");  
		sql.append("   left join DRGs.t_department dep \n ");  
		sql.append("   on a.P00300001=dep.dep_code \n ");  
		sql.append("   where to_char(a.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"' \n ");
		sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append("   and b.pardrg_code is not null \n ");
		sql.append( condition );
		sql.append("   ) \n ");
		sql.append("   group by b.pardrg_code,c.as_avg_fee \n ");
		sql.append("   ) a  \n ");
		List<Map<String,Object>>  onlyLastPeriod = jdbcTemplateObj.queryForList(sql.toString()) ;
		result.put("onlyLastPeriod",onlyLastPeriod);
		
		//仅本期覆盖
		sql.setLength(0);
		sql.append("   select count(distinct a.pardrg_code) drgCnt,sum(incasecnt) incasecnt,sum(TotalFee) TotalFee,sum(TotalFee)/sum(incasecnt) avgfee,  \n ");
		sql.append("   case when  sum(InCaseCnt)<>0 then sum(TotalWeight)/sum(InCaseCnt) else 0 end cmi,  \n ");
		sql.append("   sum(TotalWeight) TotalWeight,  \n ");
		sql.append("   sum(case when nvl(avgfee,0)-nvl(a.as_avg_fee,0)>0 then 1 else 0 end ) as overfeecnt from (  \n ");
		sql.append("   select b.pardrg_code,  \n ");
		sql.append("   sum(case when b.pardrg_code is not null and a.P00320000 < 61 then  1 else 0 end) as InCaseCnt,  \n ");
		sql.append("   sum(a.P00800000)  as TotalFee,c.as_avg_fee,  \n ");
		sql.append("   avg(a.P00800000) avgfee,  \n ");
		sql.append("   sum(case when b.pardrg_code is null then 0 else c.as_weight end) as TotalWeight  \n ");
		sql.append("   from drgs.mr_baseinfo bs left join drgs.mr_ipinfo a on bs.pmainindex=a.pmainindex   \n ");
		sql.append("   left join DRGs.t_patientdrg b   \n ");
		sql.append("   on a.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"'  \n ");
		sql.append("   left join DRGs.t_drgversion dr \n" );
		sql.append("   on b.ver_code=dr.ver_code   \n ");
		sql.append("   left join DRGs.t_department dep   \n ");
		sql.append("   on a.P00300001=dep.dep_code   \n ");
		sql.append("   left join DRGs.t_annualstd c   \n ");
		sql.append("   on b.pardrg_code=c.as_drg_code and c.as_id = '"+stdId+"'   \n ");
		sql.append("   where to_char(a.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
		sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append("   and b.pardrg_code is not null \n ");
		sql.append( condition );
		sql.append("   and b.pardrg_code  not in ( \n ");
		sql.append("   select distinct(b.pardrg_code) from DRGs.mr_ipinfo a   \n ");
		sql.append("   left join DRGs.mr_baseinfo bs on bs.pmainindex=a.pmainindex \n ");
		sql.append("   left join DRGs.t_patientdrg b  \n ");   
		sql.append("   on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"' \n ");  
		sql.append("   left join DRGs.t_department dep \n ");  
		sql.append("   on a.P00300001=dep.dep_code \n ");  
		sql.append("   where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"' \n ");
		sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append("   and b.pardrg_code is not null \n ");
		sql.append( condition );
		sql.append("   ) \n ");
		sql.append("   group by b.pardrg_code,c.as_avg_fee \n ");
		sql.append("   ) a  \n ");
		List<Map<String,Object>>  onlyThisPeriod = jdbcTemplateObj.queryForList(sql.toString()) ;
		result.put("onlyThisPeriod",onlyThisPeriod);
		//本期小
		sql.setLength(0);
		sql.append("   select count(distinct a.pardrg_code) drgCnt,sum(incasecnt) incasecnt,sum(TotalFee) TotalFee,sum(TotalFee)/sum(incasecnt) avgfee,  \n ");
		sql.append("   case when  sum(InCaseCnt)<>0 then sum(TotalWeight)/sum(InCaseCnt) else 0 end cmi,  \n ");
		sql.append("   sum(TotalWeight) TotalWeight,  \n ");
		sql.append("   sum(case when nvl(avgfee,0)-nvl(a.as_avg_fee,0)>0 then 1 else 0 end ) as overfeecnt from (  \n ");
		sql.append("   select a.pardrg_code,a.InCaseCnt,a.TotalFee,a.avgfee,a.TotalWeight,a.as_avg_fee from (  \n ");
		sql.append("   select b.pardrg_code,  \n ");
		sql.append("   sum(case when b.pardrg_code is not null and a.P00320000 < 61 then  1 else 0 end) as InCaseCnt,  \n ");
		sql.append("   sum(a.P00800000)  as TotalFee,c.as_avg_fee,  \n ");
		sql.append("   avg(a.P00800000) avgfee,  \n ");
		sql.append("   sum(case when b.pardrg_code is null then 0 else c.as_weight end) as TotalWeight  \n ");
		sql.append("   from drgs.mr_baseinfo bs left join drgs.mr_ipinfo a on bs.pmainindex=a.pmainindex   \n ");
		sql.append("   left join DRGs.t_patientdrg b    \n ");
		sql.append("   on a.pmainindex=b.parid   and  b.ver_region='"+groupScheme+"' \n ");
		sql.append("   left join DRGs.t_drgversion dr \n" );
		sql.append("   on b.ver_code=dr.ver_code   \n ");
		sql.append("   left join DRGs.t_department dep   \n ");
		sql.append("   on a.P00300001=dep.dep_code   \n ");
		sql.append("   left join DRGs.t_annualstd c   \n ");
		sql.append("   on b.pardrg_code=c.as_drg_code and c.as_id = '"+stdId+"'   \n ");
		sql.append("   where to_char(a.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
		sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append("   and b.pardrg_code is not null \n ");
		sql.append( condition );
		sql.append("   group by b.pardrg_code,c.as_avg_fee) a  \n ");
		sql.append("   left join   \n ");
		sql.append("   (  \n ");
		sql.append("   select b.pardrg_code,  \n ");
		sql.append("   avg(a.P00800000) avgfee  \n ");
		sql.append("   from DRGs.mr_ipinfo a   \n ");
		sql.append("   left join DRGs.mr_baseinfo bs on bs.pmainindex=a.pmainindex \n ");
		sql.append("   left join DRGs.t_patientdrg b    \n ");
		sql.append("   on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"'  \n ");
		sql.append("   left join DRGs.t_department dep   \n ");
		sql.append("   on a.P00300001=dep.dep_code  \n ");
		sql.append("   where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"'  \n ");
		sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append( condition );
		sql.append("   group by b.pardrg_code  \n ");
		sql.append("   ) b   \n ");
		sql.append("   on a.pardrg_code= b.pardrg_code  \n ");
		sql.append("   where a.avgfee<= b.avgfee ) a  \n ");
		List<Map<String,Object>>  thisPeriodLittle = jdbcTemplateObj.queryForList(sql.toString()) ;
		result.put("thisPeriodLittle",thisPeriodLittle);
		
		//本期大
		sql.setLength(0);
		sql.append("   select count(distinct a.pardrg_code) drgCnt,sum(incasecnt) incasecnt,sum(TotalFee) TotalFee,sum(TotalFee)/sum(incasecnt) avgfee,  \n ");
		sql.append("   case when  sum(InCaseCnt)<>0 then sum(TotalWeight)/sum(InCaseCnt) else 0 end cmi,  \n ");
		sql.append("   sum(TotalWeight) TotalWeight,  \n ");
		sql.append("   sum(case when nvl(avgfee,0)-nvl(a.as_avg_fee,0)>0 then 1 else 0 end ) as overfeecnt from (  \n ");
		sql.append("   select a.pardrg_code,a.InCaseCnt,a.TotalFee,a.avgfee,a.TotalWeight,a.as_avg_fee from (  \n ");
		sql.append("   select b.pardrg_code,  \n ");
		sql.append("   sum(case when b.pardrg_code is not null and a.P00320000 < 61 then  1 else 0 end) as InCaseCnt,  \n ");
		sql.append("   sum(a.P00800000)  as TotalFee,c.as_avg_fee,  \n ");
		sql.append("   avg(a.P00800000) avgfee,  \n ");
		sql.append("   sum(case when b.pardrg_code is null then 0 else c.as_weight end) as TotalWeight  \n ");
		sql.append("   from drgs.mr_baseinfo bs left join drgs.mr_ipinfo a on bs.pmainindex=a.pmainindex   \n ");
		sql.append("   left join DRGs.t_patientdrg b  \n ");
		sql.append("   on a.pmainindex=b.parid   and  b.ver_region='"+groupScheme+"'  \n ");
		sql.append("   left join DRGs.t_drgversion dr \n " );
		sql.append("   on b.ver_code=dr.ver_code   \n ");
		sql.append("   left join DRGs.t_department dep   \n ");
		sql.append("   on a.P00300001=dep.dep_code   \n ");
		sql.append("   left join DRGs.t_annualstd c   \n ");
		sql.append("   on b.pardrg_code=c.as_drg_code and c.as_id = '"+stdId+"'  \n ");
		sql.append("   where to_char(a.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
		sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append("   and b.pardrg_code is not null \n ");
		sql.append( condition );
		sql.append("   group by b.pardrg_code,c.as_avg_fee) a  \n ");
		sql.append("   left join   \n ");
		sql.append("   (  \n ");
		sql.append("   select b.pardrg_code,  \n ");
		sql.append("   avg(a.P00800000) avgfee  \n ");
		sql.append("   from DRGs.mr_ipinfo a   \n ");
		sql.append("   left join DRGs.mr_baseinfo bs on bs.pmainindex=a.pmainindex \n ");
		sql.append("   left join DRGs.t_patientdrg b   \n ");
		sql.append("   on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"'   \n ");
		sql.append("   left join DRGs.t_department dep   \n ");
		sql.append("   on a.P00300001=dep.dep_code  \n ");
		sql.append("   where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"'  \n ");
		sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append( condition );
		sql.append("   group by b.pardrg_code  \n ");
		sql.append("   ) b   \n ");
		sql.append("   on a.pardrg_code= b.pardrg_code  \n ");
		sql.append("   where a.avgfee> b.avgfee ) a  \n ");
		List<Map<String,Object>>  thisPeriodLarge = jdbcTemplateObj.queryForList(sql.toString()) ;
		result.put("thisPeriodLarge",thisPeriodLarge);
		return result;
	}
	
	/**
	 * 
	 * @methodname    :drgsDetail
	 * @description   : 病组结构详情
	 * @param         :
	 * @author        :mazhaoyan
	 * @param stdYear 
	 * @return        :
	 * @date          :2015年12月14日
	 */
	@Override
	@Cacheable(value = "drgsCaseDetail3.1.1", key = "'drgsDetail' + #drgsAnalysis.wardCode + #drgsAnalysis.depCode + #drgsAnalysis.depGroupCode + #drgsAnalysis.hospCode"
											+" + #drgsAnalysis.stdId + #drgsAnalysis.verRegion + #drgsAnalysis.dateFrom + #drgsAnalysis.dateTo + #drgsAnalysis.queryType ")
	public List<Map<String,Object>> drgsDetail(DrgsAnalysis drgsAnalysis) {
		String wardCode = drgsAnalysis.getWardCode();
		String depCode = drgsAnalysis.getDepCode();
		String depGroupCode = drgsAnalysis.getDepGroupCode();
		String hospCode = drgsAnalysis.getHospCode();
    	String dateFrom= drgsAnalysis.getDateFrom();
		String dateTo=drgsAnalysis.getDateTo();
		String queryType = drgsAnalysis.getQueryType();
		String stdId = drgsAnalysis.getStdId();//标杆的id
		String groupScheme = drgsAnalysis.getVerRegion();
		int thisYear = Integer.parseInt(dateFrom.substring(0,4));//当年
    	String monthFrom=dateFrom.substring(5);	//起始月份
		String monthTo=dateTo.substring(5);	//起始月份
    	String condition = "";//条件字符串
    	
    	List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
    	//如果标杆id为空则返回
    	if( !StringUtils.hasLength(stdId) || null == stdId || "".equals(stdId) || "null".equals(stdId) ){
    		return result ;
    	}
    	//如果院区编码不为空
		if(StringUtils.hasLength(hospCode)){
			condition +="   and instr('"+hospCode+"',bs.p00010001)>0 \n ";	
		}
    	//当查询条件 有科室（并且不为全部）并且无病区的情况下，查询数据添加一列 “是否理论应治疗”
    	boolean overage = false;
    	String[] stdIds = stdId.split("_");
    	String conditionOver = "";//是否在理论治疗内条件字符串
		//科室组
		if (StringUtils.hasLength(depGroupCode)&&!"null".equals(depGroupCode)&&!"hosp".equals(depGroupCode)) {
			condition += "  and dep.pardepgroup_code = '"+depGroupCode+"'";
		} 
		//科室
		if (StringUtils.hasLength(depCode)&&!"null".equals(depCode)) {
			if(depCode.indexOf(",")>-1)
			{//多个科室
				depCode = "'"+depCode.replaceAll(",","','")+"'";
				condition += "  and dep.dep_code in ("+depCode+")";
			}
			else
			{//一个科室
				condition += "  and dep.dep_code = '"+depCode+"'";
				overage = true;
				conditionOver = "   left join \n "
						+"( select d.pardrg_code from DRGs.t_departmentdrg d \n "
						+" where  d.version = '"+stdIds[0]+"' and d.pardep_code = '"+depCode+"') d \n "
						+" on t.drgCode = d.pardrg_code \n ";
			}
		}
		//病区
		if (StringUtils.hasLength(wardCode)&&!"null".equals(wardCode)) {
			condition += "  and a.P00310001 = '"+wardCode+"'";
			overage = false;
			conditionOver = "";
		}
		
		StringBuilder sql  = new StringBuilder();
		switch(queryType)
		{
		case "this":
			sql.setLength(0);
			sql.append("  select rownum lastorder,");
			if(overage){
				sql.append("  case when d.pardrg_code is not null then 1 else 0 end cover,");				
			}
			sql.append("  t.* from(");
			sql.append("  select  a.pardrg_code drgCode,a.drg_desc drgDesc,a.tpcnt tpcnt,a.avgfee avgfee,a.avgday avgday,nvl(b.lpcnt,0) lpcnt,nvl(b.lavgfee,0) lavgfee,nvl(b.lavgday,0) lavgday,c.as_weight weight from  \n ");
			sql.append("  (select a.pardrg_code,a.drg_desc,a.tpcnt,a.avgfee,a.avgday from( \n ");
			sql.append("  select b.pardrg_code,drg.drg_desc,count(*) tpcnt,avg(a.P00800000) avgfee,avg(a.P00320000) avgday \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join drgs.t_drgs drg  \n ");
			sql.append("  on drg.DRG_CODE=b.PARDRG_CODE and b.VER_CODE=drg.VER_CODE  \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"' \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append("  group by b.pardrg_code,drg.drg_desc  \n ");
			sql.append("  order by tpcnt desc ) a) a  \n ");
			sql.append("  left join  \n ");
			sql.append("  (select b.pardrg_code,b.lpcnt,b.lavgfee,b.lavgday from( \n ");
			sql.append("  select b.pardrg_code,count(*) lpcnt,avg(a.P00800000) lavgfee,avg(a.P00320000) lavgday \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"' \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append("  group by b.pardrg_code \n ");
			sql.append("  order by lpcnt desc ) b)b \n ");
			sql.append("  on a.pardrg_code = b.pardrg_code \n ");
			sql.append("  left join DRGs.t_annualstd c \n ");
			sql.append("  on c.as_drg_code = a.pardrg_code and c.as_id = '"+stdId+"' \n ");
			sql.append("  order by lpcnt desc) t \n ");
			break;
		case "last":
			sql.setLength(0);
			sql.append("  select rownum thisOrder,");
			if(overage){
				sql.append("  case when d.pardrg_code is not null then 1 else 0 end cover,");				
			}
			sql.append("  t.* from(");
			sql.append("  select b.pardrg_code drgCode,b.drg_desc drgDesc,nvl(a.tpcnt,0) tpcnt,nvl(a.avgfee,0) avgfee,nvl(a.avgday,0) avgday,b.lpcnt lpcnt,b.lavgfee lavgfee,b.lavgday lavgday,c.as_weight weight from  \n ");
			sql.append("  (select a.pardrg_code,a.tpcnt,a.avgfee,a.avgday from( \n ");
			sql.append("  select b.pardrg_code,count(*) tpcnt,avg(a.P00800000) avgfee,avg(a.P00320000) avgday \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("  left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("  left join DRGs.t_patientdrg b  \n ");
			sql.append("  on a.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"'   \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"' \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append("  group by b.pardrg_code \n ");
			sql.append("  order by tpcnt desc ) a) a  \n ");
			sql.append("  right join  \n ");
			sql.append("  (select b.pardrg_code,b.drg_desc,b.lpcnt,b.lavgfee,b.lavgday from( \n ");
			sql.append("  select b.pardrg_code,drg.drg_desc,count(*) lpcnt,avg(a.P00800000) lavgfee,avg(a.P00320000) lavgday \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"'  \n ");
			sql.append("  left join drgs.t_drgs drg  \n ");
			sql.append("  on drg.DRG_CODE=b.PARDRG_CODE and b.VER_CODE=drg.VER_CODE  \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"' \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append("  group by b.pardrg_code,drg.drg_desc \n ");
			sql.append("  order by lpcnt desc ) b)b \n ");
			sql.append("  on a.pardrg_code = b.pardrg_code \n ");
			sql.append("  left join DRGs.t_annualstd c \n ");
			sql.append("  on c.as_drg_code = b.pardrg_code and  c.as_id = '"+stdId+"'  \n ");
			sql.append("  order by tpcnt desc) t \n ");
			break;
		case "thisNo":
			sql.setLength(0);
			sql.append("  select rownum lastorder,");
			if(overage){
				sql.append("  case when d.pardrg_code is not null then 1 else 0 end cover,");				
			}
			sql.append("  t.* from(");
			sql.append("   select b.pardrg_code drgCode,b.drg_desc drgDesc,0 tpcnt,0 avgfee,0 avgday,b.lpcnt lpcnt,b.lavgfee lavgfee,b.lavgday lavgday,c.as_weight weight from   \n ");
			sql.append("   (select b.pardrg_code,b.drg_desc,b.lpcnt,b.lavgfee,b.lavgday from(  \n ");
			sql.append("   select b.pardrg_code,drg.drg_desc,count(*) lpcnt,avg(a.P00800000) lavgfee,avg(a.P00320000) lavgday  \n ");
			sql.append("   from DRGs.mr_ipinfo a   \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("   left join DRGs.t_patientdrg b    \n ");
			sql.append("   on a.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"'  \n ");
			sql.append("   left join drgs.t_drgs drg  \n ");
			sql.append("   on drg.DRG_CODE=b.PARDRG_CODE and b.VER_CODE=drg.VER_CODE  \n ");
			sql.append("   left join DRGs.t_department dep   \n ");
			sql.append("   on a.P00300001=dep.dep_code   \n ");
			sql.append("   where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"'  \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("   and b.pardrg_code is not null  \n ");
			sql.append("   and b.pardrg_code not in ( \n ");
			sql.append("   select distinct(b.pardrg_code) from DRGs.mr_ipinfo a    \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("   left join DRGs.t_patientdrg b      \n ");
			sql.append("   on a.pmainindex=b.parid   and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("   left join DRGs.t_department dep    \n ");
			sql.append("   on a.P00300001=dep.dep_code    \n ");
			sql.append("   where to_char(a.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
			sql.append("   and b.pardrg_code is not null  \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("   ) \n ");
			sql.append("   group by b.pardrg_code,drg.drg_desc  \n ");
			sql.append("   order by lpcnt desc ) b)b  \n ");
			sql.append("   left join DRGs.t_annualstd c  \n ");
			sql.append("   on c.as_drg_code = b.pardrg_code and c.as_id = '"+stdId+"'  \n ");
			sql.append("   order by lpcnt desc) t \n ");
			break;
		case "lastNo":
			sql.setLength(0);
			sql.append("  select rownum thisOrder,");
			if(overage){
				sql.append("  case when d.pardrg_code is not null then 1 else 0 end cover,");				
			}
			sql.append("  t.* from(");
			sql.append("   select  a.pardrg_code drgCode ,a.drg_desc drgDesc,a.tpcnt,a.avgfee avgfee,a.avgday avgday,0 lpcnt,0 lavgfee,0 lavgday,c.as_weight weight from   \n ");
			sql.append("   (select a.pardrg_code,a.drg_desc,a.tpcnt,a.avgfee,a.avgday from(  \n ");
			sql.append("   select b.pardrg_code,drg.drg_desc,count(*) tpcnt,avg(a.P00800000) avgfee,avg(a.P00320000) avgday  \n ");
			sql.append("   from DRGs.mr_ipinfo a   \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("   left join DRGs.t_patientdrg b    \n ");
			sql.append("   on a.pmainindex=b.parid   and  b.ver_region='"+groupScheme+"'  \n ");
			sql.append("   left join drgs.t_drgs drg   \n ");
			sql.append("   on drg.DRG_CODE=b.PARDRG_CODE and b.VER_CODE=drg.VER_CODE   \n ");
			sql.append("   left join DRGs.t_department dep   \n ");
			sql.append("   on a.P00300001=dep.dep_code   \n ");
			sql.append("   where to_char(a.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("   and b.pardrg_code is not null  \n ");
			sql.append("   and  b.pardrg_code not in( \n ");
			sql.append("   select distinct(b.pardrg_code) from DRGs.mr_ipinfo a     \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("   left join DRGs.t_patientdrg b     \n ");
			sql.append("   on a.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"'     \n ");
			sql.append("   left join DRGs.t_department dep     \n ");
			sql.append("   on a.P00300001=dep.dep_code     \n ");
			sql.append("   where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"'   \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("   and b.pardrg_code is not null   \n ");
			sql.append("   ) \n ");
			sql.append("   group by b.pardrg_code,drg.drg_desc   \n ");
			sql.append("   order by tpcnt desc ) a) a   \n ");
			sql.append("   left join DRGs.t_annualstd c  \n ");
			sql.append("   on c.as_drg_code = a.pardrg_code and c.as_id = '"+stdId+"' \n ");
			sql.append("   order by tpcnt desc) t  \n ");
			break;
		case "thisSmall":
			sql.setLength(0);
			sql.append("  select rownum lastorder,");
			if(overage){
				sql.append("  case when d.pardrg_code is not null then 1 else 0 end cover,");				
			}
			sql.append("  t.* from(");
			sql.append("  select  a.pardrg_code drgCode,a.drg_desc drgDesc,a.tpcnt tpcnt,a.avgfee avgfee,a.avgday avgday,b.lpcnt lpcnt,b.lavgfee lavgfee,b.lavgday lavgday,c.as_weight weight from  \n ");
			sql.append("  (select a.pardrg_code,a.drg_desc,a.tpcnt,a.avgfee,a.avgday from( \n ");
			sql.append("  select b.pardrg_code,drg.drg_desc,count(*) tpcnt,avg(a.P00800000) avgfee,avg(a.P00320000) avgday \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("  left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid   and  b.ver_region='"+groupScheme+"'\n ");
			sql.append("  left join drgs.t_drgs drg  \n ");
			sql.append("  on drg.DRG_CODE=b.PARDRG_CODE and b.VER_CODE=drg.VER_CODE  \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"' \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append("  group by b.pardrg_code,drg.drg_desc  \n ");
			sql.append("  order by tpcnt desc ) a) a  \n ");
			sql.append("  left join  \n ");
			sql.append("  (select b.pardrg_code,b.lpcnt,b.lavgfee,b.lavgday from( \n ");
			sql.append("  select b.pardrg_code,count(*) lpcnt,avg(a.P00800000) lavgfee,avg(a.P00320000) lavgday \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid   and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"' \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append("  group by b.pardrg_code \n ");
			sql.append("  order by lpcnt desc ) b)b \n ");
			sql.append("  on a.pardrg_code = b.pardrg_code \n ");
			sql.append("  left join DRGs.t_annualstd c \n ");
			sql.append("  on c.as_drg_code = a.pardrg_code and c.as_id = '"+stdId+"' \n ");
			sql.append("  where a.avgfee <= b.lavgfee \n ");
			sql.append("  order by lpcnt desc) t \n ");
			break;
		case "thisBig":
			sql.setLength(0);
			sql.append("  select rownum lastorder,");
			if(overage){
				sql.append("  case when d.pardrg_code is not null then 1 else 0 end cover,");				
			}
			sql.append("  t.* from(");
			sql.append("  select  a.pardrg_code drgCode,a.drg_desc drgDesc,a.tpcnt tpcnt,a.avgfee avgfee,a.avgday avgday,b.lpcnt lpcnt,b.lavgfee lavgfee,b.lavgday lavgday,c.as_weight weight from  \n ");
			sql.append("  (select a.pardrg_code,a.drg_desc,a.tpcnt,a.avgfee,a.avgday from( \n ");
			sql.append("  select b.pardrg_code,drg.drg_desc,count(*) tpcnt,avg(a.P00800000) avgfee,avg(a.P00320000) avgday \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join drgs.t_drgs drg  \n ");
			sql.append("  on drg.DRG_CODE=b.PARDRG_CODE and b.VER_CODE=drg.VER_CODE  \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"' \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append("  group by b.pardrg_code,drg.drg_desc  \n ");
			sql.append("  order by tpcnt desc ) a) a  \n ");
			sql.append("  left join  \n ");
			sql.append("  (select b.pardrg_code,b.lpcnt,b.lavgfee,b.lavgday from( \n ");
			sql.append("  select b.pardrg_code,count(*) lpcnt,avg(a.P00800000) lavgfee,avg(a.P00320000) lavgday \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"' \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append("  group by b.pardrg_code \n ");
			sql.append("  order by lpcnt desc ) b)b \n ");
			sql.append("  on a.pardrg_code = b.pardrg_code \n ");
			sql.append("  left join DRGs.t_annualstd c \n ");
			sql.append("  on c.as_drg_code = a.pardrg_code and c.as_id = '"+stdId+"' \n ");
			sql.append("  where a.avgfee > b.lavgfee \n ");
			sql.append("  order by lpcnt desc) t \n ");
			break;
		}
		//追加查询条件
		if(overage){
			sql.append( conditionOver );				
		}
		result = jdbcTemplateObj.queryForList(sql.toString()) ;
		return result;
	}
	
	/**
	 * 
	 * @methodname    :overproofDrgsDetail
	 * @description   : 费用超标DRGs病组详情
	 * @param         :
	 * @author        :fulianxia
	 * @return        :
	 * @date          :2016年3月25日
	 */
	@Override
	@Cacheable(value = "drgsCaseDetail3.1.1", key = "'overproofDrgsDetail' + #drgsAnalysis.wardCode + #drgsAnalysis.depCode + #drgsAnalysis.depGroupCode + #drgsAnalysis.hospCode "
											+" + #drgsAnalysis.stdId + #drgsAnalysis.verRegion + #drgsAnalysis.dateFrom + #drgsAnalysis.dateTo + #drgsAnalysis.queryType")
	public List<Map<String,Object>> overproofDrgsDetail(DrgsAnalysis drgsAnalysis) {
		String wardCode = drgsAnalysis.getWardCode();
		String depCode = drgsAnalysis.getDepCode();
		String depGroupCode = drgsAnalysis.getDepGroupCode();
		String hospCode = drgsAnalysis.getHospCode();
    	String dateFrom= drgsAnalysis.getDateFrom();
		String dateTo=drgsAnalysis.getDateTo();
		String queryType = drgsAnalysis.getQueryType();
		String stdId = drgsAnalysis.getStdId();//标杆的id
		String groupScheme = drgsAnalysis.getVerRegion();
		
		int thisYear = Integer.parseInt(dateFrom.substring(0,4));//当年
    	String monthFrom=dateFrom.substring(5);	//起始月份
		String monthTo=dateTo.substring(5);	//起始月份
    	String condition = "";//条件字符串
		
    	List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
    	//如果标杆id为空则返回
    	if( !StringUtils.hasLength(stdId) || null == stdId || "".equals(stdId) || "null".equals(stdId) ){
    		return result ;
    	}
    	//如果院区编码不为空
		if(StringUtils.hasLength(hospCode)){
			condition +="   and instr('"+hospCode+"',bs.p00010001)>0 \n ";	
		}
    	//当查询条件 有科室（并且不为全部）并且无病区的情况下，查询数据添加一列 “是否理论应治疗”
    	boolean overage = false;
    	String[] stdIds = stdId.split("_");
    	String conditionOver = "";//是否在理论治疗内条件字符串
		//科室组
		if (StringUtils.hasLength(depGroupCode)&&!"null".equals(depGroupCode)&&!"hosp".equals(depGroupCode)) {
			condition += "  and dep.pardepgroup_code = '"+depGroupCode+"'";
		} 
		//科室
		if (StringUtils.hasLength(depCode)&&!"null".equals(depCode)) {
			if(depCode.indexOf(",")>-1)
			{//多个科室
				depCode = "'"+depCode.replaceAll(",","','")+"'";
				condition += "  and dep.dep_code in ("+depCode+")";
			}
			else
			{//一个科室
				condition += "  and dep.dep_code = '"+depCode+"'";
				overage = true;
				conditionOver = "   left join \n "
						+"( select d.pardrg_code from DRGs.t_departmentdrg d \n "
						+" where  d.version = '"+stdIds[0]+"' and d.pardep_code = '"+depCode+"') d \n "
						+" on a.pardrg_code= d.pardrg_code \n ";
			}
		}
		//病区
		if (StringUtils.hasLength(wardCode)&&!"null".equals(wardCode)) {
			condition += "  and a.P00310001 = '"+wardCode+"'";
			overage = false;
			conditionOver = "";
		}
		StringBuilder sql  = new StringBuilder();
		switch(queryType)
		{
		case "this":
			sql.setLength(0);
			sql.append("  select distinct  a.pardrg_code drgCode,a.drg_desc drgDesc,a.as_weight weight,a.InCaseCnt inCaseCnt,a.avgfee avgfee,a.avgday avgday,a.as_avg_fee ,a.as_avg_days \n ");
			if(overage){
				sql.append("  ,case when d.pardrg_code is not null then 1 else 0 end cover ");				
			}
			sql.append("  from ( select  b.pardrg_code,drg.drg_desc,c.as_weight, \n ");
			sql.append("  sum(case when b.pardrg_code is not null and a.P00320000 < 61 then  1 else 0 end) as InCaseCnt, \n ");
			sql.append("   avg(a.P00800000) avgfee, avg(a.P00320000) avgday,c.as_avg_fee,c.as_avg_days   \n ");
			sql.append("  from DRGs.mr_ipinfo a   \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on bs.pmainindex=a.pmainindex \n ");
			sql.append("  left join DRGs.t_patientdrg b    \n ");
			sql.append("  on a.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"'\n ");
			sql.append("  left join drgs.t_drgs drg  \n ");
			sql.append("  on drg.DRG_CODE=b.PARDRG_CODE and b.VER_CODE=drg.VER_CODE  \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  left join DRGs.t_annualstd c  \n ");
			sql.append("  on b.pardrg_code=c.as_drg_code and as_id='"+stdId+"'  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"' \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append("  group by b.pardrg_code,drg.drg_desc,as_weight,c.as_avg_fee,c.as_avg_days) a  \n ");
			if(overage){
				sql.append(conditionOver);				
			}			
			sql.append("  where 1=1 and nvl(a.avgfee,0)-nvl(a.as_avg_fee,0)>0  \n ");
			sql.append("  order by incasecnt desc \n ");
			break;
		case "last":
			sql.setLength(0);
			sql.append("  select distinct  a.pardrg_code drgCode,a.drg_desc drgDesc,a.as_weight weight,a.InCaseCnt inCaseCnt,a.avgfee avgfee,a.avgday avgday,a.as_avg_fee ,a.as_avg_days   \n ");
			if(overage){
				sql.append("  ,case when d.pardrg_code is not null then 1 else 0 end cover ");				
			}
			sql.append("  from ( select  b.pardrg_code,drg.drg_desc,c.as_weight, \n ");
			sql.append("  sum(case when b.pardrg_code is not null and a.P00320000 < 61 then  1 else 0 end) as InCaseCnt, \n ");
			sql.append("  avg(a.P00800000) avgfee, avg(a.P00320000) avgday,c.as_avg_fee,c.as_avg_days  \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on bs.pmainindex=a.pmainindex \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join DRGs.t_Drgs drg   \n ");
			sql.append("  on b.pardrg_code=drg.drg_code and b.ver_code=drg.ver_code  \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  left join DRGs.t_annualstd c   \n ");
			sql.append("  on b.pardrg_code=c.as_drg_code and as_id='"+stdId+"'  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"' \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append("  group by b.pardrg_code,drg.drg_desc,as_weight,c.as_avg_fee,c.as_avg_days) a  \n ");
			if(overage){
				sql.append(conditionOver);				
			}
			sql.append("  where 1=1  \n ");
			sql.append("  and nvl(a.avgfee,0)-nvl(a.as_avg_fee,0)>0  \n ");
			sql.append("  order by incasecnt desc  \n ");
			break;
		case "thisNo":
			sql.setLength(0);
			sql.append("   select distinct  a.pardrg_code drgCode,drg.drg_desc drgDesc,a.as_weight weight,a.InCaseCnt inCaseCnt,a.avgfee avgfee,a.avgday avgday,a.as_avg_fee ,a.as_avg_days  \n ");
			if(overage){
				sql.append("  ,case when d.pardrg_code is not null then 1 else 0 end cover ");				
			}
			sql.append("   from (  select  b.pardrg_code,b.ver_code,c.as_weight, \n ");
			sql.append("   sum(case when b.pardrg_code is not null and a.P00320000 < 61 then  1 else 0 end) as InCaseCnt,  \n ");
			sql.append("   avg(a.P00800000) avgfee, avg(a.P00320000) avgday,c.as_avg_fee,c.as_avg_days  \n ");
			sql.append("   from DRGs.mr_ipinfo a   \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on bs.pmainindex=a.pmainindex \n ");
			sql.append("   left join DRGs.t_patientdrg b   \n ");
			sql.append("   on a.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"'  \n ");
			sql.append("   left join DRGs.t_department dep   \n ");
			sql.append("   on a.P00300001=dep.dep_code   \n ");
			sql.append("   left join DRGs.t_annualstd c   \n ");
			sql.append("   on b.pardrg_code=c.as_drg_code and as_id='"+stdId+"'   \n ");
			sql.append("   where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"'  \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("   and b.pardrg_code is not null  \n ");
			sql.append("   and b.pardrg_code not in ( \n ");
			sql.append("   select distinct(b.pardrg_code) from DRGs.mr_ipinfo a    \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on bs.pmainindex=a.pmainindex \n ");
			sql.append("   left join DRGs.t_patientdrg b     \n ");
			sql.append("   on a.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"'  \n ");
			sql.append("   left join DRGs.t_department dep    \n ");
			sql.append("   on a.P00300001=dep.dep_code    \n ");
			sql.append("   where to_char(a.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
			sql.append("   and b.pardrg_code is not null  \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("   ) \n ");
			sql.append("   group by b.pardrg_code,as_weight,b.ver_code,c.as_avg_fee,c.as_avg_days) a  \n ");
			sql.append("   left join DRGs.t_Drgs drg  \n ");
			sql.append("   on a.pardrg_code=drg.drg_code and a.ver_code=drg.ver_code  \n ");
			if(overage){
				sql.append(conditionOver);				
			}
			sql.append("   where 1=1  \n ");
			sql.append("   and nvl(a.avgfee,0)-nvl(a.as_avg_fee,0)>0  \n ");
			sql.append("   order by incasecnt desc  \n ");
			break;
		case "lastNo":
			sql.setLength(0);
			sql.append("   select distinct  a.pardrg_code drgCode,drg.drg_desc drgDesc,a.as_weight weight,a.InCaseCnt inCaseCnt,a.avgfee avgfee,a.avgday avgday,a.as_avg_fee ,a.as_avg_days    \n ");
			if(overage){
				sql.append("  ,case when d.pardrg_code is not null then 1 else 0 end cover ");				
			}
			sql.append("   from ( select  b.pardrg_code,b.ver_code,c.as_weight, \n ");
			sql.append("   sum(case when b.pardrg_code is not null and a.P00320000 < 61 then  1 else 0 end) as InCaseCnt,  \n ");
			sql.append("   avg(a.P00800000) avgfee, avg(a.P00320000) avgday,c.as_avg_fee,c.as_avg_days  \n ");
			sql.append("   from DRGs.mr_ipinfo a   \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on bs.pmainindex=a.pmainindex \n ");
			sql.append("   left join DRGs.t_patientdrg b   \n ");
			sql.append("   on a.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"'  \n ");
			sql.append("   left join DRGs.t_department dep   \n ");
			sql.append("   on a.P00300001=dep.dep_code   \n ");
			sql.append("   left join DRGs.t_annualstd c   \n ");
			sql.append("   on b.pardrg_code=c.as_drg_code and as_id='"+stdId+"'   \n ");
			sql.append("   where to_char(a.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("   and b.pardrg_code is not null  \n ");
			sql.append("   and b.pardrg_code not in ( \n ");
			sql.append("   select distinct(b.pardrg_code) from DRGs.mr_ipinfo a    \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on bs.pmainindex=a.pmainindex \n ");
			sql.append("   left join DRGs.t_patientdrg b     \n ");
			sql.append("   on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"'   \n ");
			sql.append("   left join DRGs.t_department dep    \n ");
			sql.append("   on a.P00300001=dep.dep_code    \n ");
			sql.append("   where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"'  \n ");
			sql.append("   and b.pardrg_code is not null  \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("   ) \n ");
			sql.append("   group by b.pardrg_code,as_weight,b.ver_code,c.as_avg_fee,c.as_avg_days) a  \n ");
			sql.append("   left join DRGs.t_Drgs drg  \n ");
			sql.append("   on a.pardrg_code=drg.drg_code and a.ver_code=drg.ver_code  \n ");
			if(overage){
				sql.append(conditionOver);				
			}
			sql.append("   where 1=1  \n ");
			sql.append("   and nvl(a.avgfee,0)-nvl(a.as_avg_fee,0)>0  \n ");
			sql.append("   order by incasecnt desc  \n ");
			break;
		case "thisSmall":
			sql.setLength(0);
			sql.append("  select distinct a.pardrg_code drgCode,a.drg_desc drgDesc,a.as_weight weight,a.InCaseCnt inCaseCnt,a.avgfee avgfee,a.avgday avgday,a.as_avg_fee ,a.as_avg_days  \n ");
			if(overage){
				sql.append("  ,case when d.pardrg_code is not null then 1 else 0 end cover ");				
			}
			sql.append("  from ( select  b.pardrg_code,drg.drg_desc,c.as_weight, \n ");
			sql.append("  sum(case when b.pardrg_code is not null and a.P00320000 < 61 then  1 else 0 end) as InCaseCnt,  \n ");
			sql.append("  avg(a.P00800000) avgfee, avg(a.P00320000) avgday,c.as_avg_fee,c.as_avg_days  \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on bs.pmainindex=a.pmainindex \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join drgs.t_drgs drg  \n ");
			sql.append("  on drg.DRG_CODE=b.PARDRG_CODE and b.VER_CODE=drg.VER_CODE  \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  left join DRGs.t_annualstd c  \n ");
			sql.append("  on b.pardrg_code=c.as_drg_code and as_id='"+stdId+"' \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"' \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append("  group by b.pardrg_code,drg.drg_desc,as_weight,c.as_avg_fee,c.as_avg_days) a  \n ");
			sql.append("  left join  \n ");
			sql.append("  (select b.pardrg_code,avg(P00800000) avgfee \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on bs.pmainindex=a.pmainindex \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"' \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append("  group by b.pardrg_code) b \n ");
			sql.append("  on a.pardrg_code = b.pardrg_code \n ");
			if(overage){
				sql.append(conditionOver);				
			}
			sql.append("  where 1=1  and nvl(a.avgfee,0)-nvl(a.as_avg_fee,0)>0 \n ");
			sql.append("  and a.avgfee <= b.avgfee \n ");
			sql.append("  order by incasecnt desc \n ");
			break;
		case "thisBig":
			sql.setLength(0);
			sql.append("  select distinct  a.pardrg_code drgCode,a.drg_desc drgDesc,a.as_weight weight,a.InCaseCnt inCaseCnt,a.avgfee avgfee,a.avgday avgday,a.as_avg_fee ,a.as_avg_days  \n ");
			if(overage){
				sql.append("  ,case when d.pardrg_code is not null then 1 else 0 end cover ");				
			}
			sql.append("  from ( select  b.pardrg_code,drg.drg_desc,c.as_weight, \n ");
			sql.append("  sum(case when b.pardrg_code is not null and a.P00320000 < 61 then  1 else 0 end) as InCaseCnt,  \n ");
			sql.append("  avg(a.P00800000) avgfee, avg(a.P00320000) avgday,c.as_avg_fee,c.as_avg_days  \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on bs.pmainindex=a.pmainindex \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join drgs.t_drgs drg  \n ");
			sql.append("  on drg.DRG_CODE=b.PARDRG_CODE and b.VER_CODE=drg.VER_CODE  \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  left join DRGs.t_annualstd c  \n ");
			sql.append("  on b.pardrg_code=c.as_drg_code and as_id='"+stdId+"' \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"' \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append("  group by b.pardrg_code,drg.drg_desc,as_weight,c.as_avg_fee,c.as_avg_days) a  \n ");
			sql.append("  left join  \n ");
			sql.append("  (select b.pardrg_code,avg(P00800000) avgfee \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on bs.pmainindex=a.pmainindex \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"' \n ");
//			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append("  group by b.pardrg_code) b \n ");
			sql.append("  on a.pardrg_code = b.pardrg_code \n ");
			if(overage){
				sql.append(conditionOver);				
			}
			sql.append("  where 1=1  and nvl(a.avgfee,0)-nvl(a.as_avg_fee,0)>0 \n ");
			sql.append("  and a.avgfee> b.avgfee \n ");
			sql.append("  order by incasecnt desc \n ");
			break;
		}
		result = jdbcTemplateObj.queryForList(sql.toString()) ;
		return result;
	}
	
	/**
	 * 
	 * @methodname    :loadCase
	 * @description   : 病例数
	 * @param         :
	 * @author        :fulianxia
	 * @return        :
	 * @date          :2016年11月28日
	 */
	@Override
	@Cacheable(value = "drgsCaseDetail3.1.1", key = "'loadCase' + #drgsAnalysis.wardCode + #drgsAnalysis.depCode + #drgsAnalysis.depGroupCode  + #drgsAnalysis.hospCode "
			+ " + #drgsAnalysis.stdId + #drgsAnalysis.verRegion + #drgsAnalysis.dateFrom + #drgsAnalysis.dateTo + #drgsAnalysis.monitorType")
	public List<Map<String,Object>> loadCase(DrgsAnalysis drgsAnalysis) {
		String wardCode = drgsAnalysis.getWardCode();
		String depCode = drgsAnalysis.getDepCode();
		String depGroupCode = drgsAnalysis.getDepGroupCode();
		String hospCode = drgsAnalysis.getHospCode();
		String dateFrom = DrgsIndex.formateDateFrom(drgsAnalysis.getDateFrom()).substring(0,7);
		String dateTo = DrgsIndex.formateDateTo( drgsAnalysis.getDateTo()).substring(0,7);
		String monitorType = drgsAnalysis.getMonitorType();
		String stdId=drgsIndexCalFacade.getStdID(dateFrom, dateTo);//标杆的id
		String groupScheme = drgsAnalysis.getVerRegion();
		
		String monthFrom=dateFrom.substring(5);	//起始月份
		String monthTo=dateTo.substring(5);	//起始月份
    	String condition = "";//条件字符串
    	
    	//获取标杆年份
    	int thisYear = Integer.parseInt(dateFrom.substring(0,4));//当年

		//科室组
		if (StringUtils.hasLength(depGroupCode)&&!"null".equals(depGroupCode)&&!"hosp".equals(depGroupCode)) {
			condition += " and dep.pardepgroup_code = '"+depGroupCode+"'";
		} 
		//科室
		if (StringUtils.hasLength(depCode)&&!"null".equals(depCode)) {
			if(depCode.indexOf(",")>-1)
			{//多个科室
				depCode = "'"+depCode.replaceAll(",","','")+"'";
				condition += " and dep.dep_code in ("+depCode+")";
			}
			else
			{//一个科室
				condition += " and dep.dep_code = '"+depCode+"'";
			}
		}
		//病区
		if (StringUtils.hasLength(wardCode)&&!"null".equals(wardCode)) {
			condition += " and ip.P00310001 = '"+wardCode+"'";
		}
		StringBuilder sql  = new StringBuilder();
		//本期覆盖
		if("thisPeriod".equals(monitorType)){
			sql.setLength(0);
			//P00050000-病案号 P00060000-姓名 P00070001-性别编码 P00090000-年龄 P00250000-入院日期 P00290000-出院日期
			//P00320000-住院天数 P00800000-总费用 PD0100-诊断描述 PO0100-手术描述
			sql.append("  select ip.pmainindex id,BASE.P00040000 IP_COUNT,base.P00050000 mrNo,base.P00060000 patientName,base.P00070001 sex,base.P00090000 ageYear,TO_CHAR(IP.P00250000, 'YYYY-MM-DD') inDate,TO_CHAR(IP.P00290000, 'YYYY-MM-DD') outDate, \n ");
			sql.append("  dep.dep_name outDep,ip.P00320000 inDays,ip.P00800000 costamount,expsum.drug_cost drugcost,'('||diag.PD0101||')'||diag.PD0100 diagnoseDessmain,pd.pardrg_code drgcode, \n ");
			sql.append("  drg.drg_desc drg,std.as_weight weight,std.as_avg_days avgDaysStd,std.as_avg_fee avgFeeStd, operinfo.operinfo operation ,diaginfo.diaginfo otherdiag \n ");
			sql.append("  from drgs.mr_ipinfo ip left join drgs.t_patientdrg pd  \n ");
			sql.append("  on ip.pmainindex = pd.parid  and  pd.ver_region='"+groupScheme+"'\n ");
			sql.append("  left join drgs.mr_baseinfo base on ip.pmainindex = base.pmainindex  \n ");
			sql.append("  left join drgs.mr_expense_sum expsum on ip.pmainindex = expsum.pmainindex \n ");
			sql.append("  left join drgs.mr_diagnose diag \n ");
			sql.append("   on ip.pmainindex=diag.pmainindex  and diag.PDType='M' \n ");
			sql.append("  left join (select diag.pmainindex, listagg('('||diag.PD0101||')'||diag.PD0100,'|')  within group (order by diag.pdindex)  diaginfo \n ");
			sql.append("  from drgs.mr_diagnose diag left join drgs.mr_ipinfo ip  on diag.pmainindex = ip.pmainindex   \n ");
			sql.append("  where to_char(ip.p00290000,'yyyy-mm') between  '"+dateFrom+"' and '"+dateTo+"' and diag.pdtype = 'O' \n");
			sql.append("  group by diag.pmainindex) diaginfo on ip.pmainindex = diaginfo.pmainindex \n");
			sql.append("  LEFT JOIN (SELECT OPER.PMAININDEX, LISTAGG('('||OPER.PO0101||')'||OPER.PO0100,'|')  WITHIN GROUP (ORDER BY OPER.POINDEX)  OPERINFO \n");
			sql.append("  FROM DRGS.MR_OPERATION OPER LEFT JOIN DRGS.MR_IPINFO IP  ON OPER.PMAININDEX = IP.PMAININDEX \n");
			sql.append("  WHERE TO_CHAR(IP.P00290000,'YYYY-MM') BETWEEN  '"+dateFrom+"' AND '"+dateTo+"' \n");
			sql.append("  GROUP BY OPER.PMAININDEX) OPERINFO ON IP.PMAININDEX = OPERINFO.PMAININDEX \n");
			sql.append("  left join drgs.t_drgs drg \n ");
			sql.append("  on pd.pardrg_code = drg.drg_code and pd.ver_code = drg.ver_code \n ");
			sql.append("  left join DRGs.t_Adrg adrg \n ");
			sql.append("  on adrg.adrg_code =drg.adrg_code and drg.ver_code = adrg.ver_code \n ");
			sql.append("  left join DRGs.t_Mdcgroup mdc \n ");
			sql.append("  on mdc.mdc_code =adrg.mdc_code and mdc.ver_code = adrg.ver_code \n ");
			sql.append("  left join drgs.t_department dep  \n ");
			sql.append("  on ip.P00300001 = dep.dep_code  \n ");
			sql.append("  left join drgs.t_depgroup dg  \n ");
			sql.append("  on dg.depgroup_code = dep.pardepgroup_code  \n ");
			sql.append("   left join DRGs.t_drgversion dr \n " );
			sql.append("   on pd.ver_code=dr.ver_code   \n ");
			sql.append("  left join drgs.t_annualstd std \n ");
			sql.append("  on pd.pardrg_code = std.as_drg_code and std.as_id = '"+stdId+"' \n ");
			sql.append("  where to_char(ip.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
			sql.append("  and instr('"+hospCode+"',base.p00010001)>0 \n ");
			sql.append(condition);
			sql.append("  and pd.pardrg_code is not null and ip.P00320000 < 61   \n ");
		}
		//同期覆盖
		if("lastPeriod".equals(monitorType)){
			sql.setLength(0);
			//P00050000-病案号 P00060000-姓名 P00070001-性别编码 P00090000-年龄 P00250000-入院日期 P00290000-出院日期
			//P00320000-住院天数 P00800000-总费用 PD0100-诊断描述 PO0100-手术描述
			sql.append("  select ip.pmainindex id,BASE.P00040000 IP_COUNT,base.P00050000 mrNo ,base.P00060000 patientName,base.P00070001 sex,base.P00090000 ageYear,TO_CHAR(IP.P00250000, 'YYYY-MM-DD') inDate,TO_CHAR(IP.P00290000, 'YYYY-MM-DD') outDate, \n ");
			sql.append("  dep.dep_name outDep,ip.P00320000 inDays,ip.P00800000 costamount,expsum.drug_cost drugcost,'('||diag.PD0101||')'||diag.PD0100 diagnoseDessmain,pd.pardrg_code drgcode, \n ");
			sql.append("  drg.drg_desc drg,std.as_weight weight,std.as_avg_days avgDaysStd,std.as_avg_fee avgFeeStd, operinfo.operinfo operation ,diaginfo.diaginfo otherdiag \n ");
			sql.append("  from drgs.mr_ipinfo ip left join drgs.t_patientdrg pd  \n ");
			sql.append("  on ip.pmainindex = pd.parid  and  pd.ver_region='"+groupScheme+"'\n ");
			sql.append("  left join drgs.mr_baseinfo base on ip.pmainindex = base.pmainindex  \n ");
			sql.append("  left join drgs.mr_expense_sum expsum on ip.pmainindex = expsum.pmainindex \n ");
			sql.append("  left join drgs.mr_diagnose diag \n ");
			sql.append("   on ip.pmainindex=diag.pmainindex  and diag.PDType='M' \n ");
			sql.append("  left join (select diag.pmainindex, listagg('('||diag.PD0101||')'||diag.PD0100,'|')  within group (order by diag.pdindex)  diaginfo \n ");
			sql.append("  from drgs.mr_diagnose diag left join drgs.mr_ipinfo ip  on diag.pmainindex = ip.pmainindex   \n ");
			sql.append("  where to_char(ip.p00290000,'yyyy-mm') between  '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"' and diag.pdtype = 'O' \n");
			sql.append("  group by diag.pmainindex) diaginfo on ip.pmainindex = diaginfo.pmainindex \n");
			sql.append("  LEFT JOIN (SELECT OPER.PMAININDEX, LISTAGG('('||OPER.PO0101||')'||OPER.PO0100,'|')  WITHIN GROUP (ORDER BY OPER.POINDEX)  OPERINFO \n");
			sql.append("  FROM DRGS.MR_OPERATION OPER LEFT JOIN DRGS.MR_IPINFO IP  ON OPER.PMAININDEX = IP.PMAININDEX \n");
			sql.append("  WHERE TO_CHAR(IP.P00290000,'YYYY-MM') BETWEEN  '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"' \n");
			sql.append("  GROUP BY OPER.PMAININDEX) OPERINFO ON IP.PMAININDEX = OPERINFO.PMAININDEX \n");
			sql.append("  left join drgs.t_drgs drg \n ");
			sql.append("  on pd.pardrg_code = drg.drg_code and pd.ver_code = drg.ver_code \n ");
			sql.append("  left join DRGs.t_Adrg adrg \n ");
			sql.append("  on adrg.adrg_code =drg.adrg_code and drg.ver_code = adrg.ver_code \n ");
			sql.append("  left join DRGs.t_Mdcgroup mdc \n ");
			sql.append("  on mdc.mdc_code =adrg.mdc_code and mdc.ver_code = adrg.ver_code \n ");
			sql.append("  left join drgs.t_department dep  \n ");
			sql.append("  on ip.P00300001 = dep.dep_code  \n ");
			sql.append("  left join drgs.t_depgroup dg  \n ");
			sql.append("  on dg.depgroup_code = dep.pardepgroup_code  \n ");
			sql.append("   left join DRGs.t_drgversion dr \n " );
			sql.append("   on pd.ver_code=dr.ver_code   \n ");
			sql.append("  left join drgs.t_annualstd std \n ");
			sql.append("  on pd.pardrg_code = std.as_drg_code and std.as_id = '"+stdId+"' \n ");
			sql.append("  where to_char(ip.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"'  \n ");
			sql.append("  and instr('"+hospCode+"',base.p00010001)>0 \n ");
			sql.append(condition);
			sql.append("  and pd.pardrg_code is not null and ip.P00320000 < 61   \n ");
		}
		
		//仅同期覆盖
		if("onlyLastPeriod".equals(monitorType)){
			sql.setLength(0);
			//P00050000-病案号 P00060000-姓名 P00070001-性别编码 P00090000-年龄 P00250000-入院日期 P00290000-出院日期
			//P00320000-住院天数 P00800000-总费用 PD0100-诊断描述 PO0100-手术描述
			sql.append("  select ip.pmainindex id,BASE.P00040000 IP_COUNT,base.P00050000 mrNo,base.P00060000 patientName,base.P00070001 sex,base.P00090000 ageYear,TO_CHAR(IP.P00250000, 'YYYY-MM-DD') inDate,TO_CHAR(IP.P00290000, 'YYYY-MM-DD') outDate, \n ");
			sql.append("  dep.dep_name outDep,ip.P00320000 inDays,ip.P00800000 costamount,expsum.drug_cost drugcost ,'('||diag.PD0101||')'||diag.PD0100 diagnoseDessmain,pd.pardrg_code drgcode, \n ");
			sql.append("  drg.drg_desc drg,std.as_weight weight,std.as_avg_days avgDaysStd,std.as_avg_fee avgFeeStd, operinfo.operinfo operation ,diaginfo.diaginfo otherdiag  \n ");
			sql.append("  from drgs.mr_ipinfo ip left join drgs.t_patientdrg pd  \n ");
			sql.append("  on ip.pmainindex = pd.parid and  pd.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join drgs.mr_baseinfo base on ip.pmainindex = base.pmainindex  \n ");
			sql.append("  left join drgs.mr_expense_sum expsum on ip.pmainindex = expsum.pmainindex \n ");
			sql.append("  left join drgs.mr_diagnose diag \n ");
			sql.append("  on ip.pmainindex=diag.pmainindex  and diag.PDType='M' \n ");
			sql.append("  left join (select diag.pmainindex, listagg('('||diag.PD0101||')'||diag.PD0100,'|')  within group (order by diag.pdindex)  diaginfo \n ");
			sql.append("  from drgs.mr_diagnose diag left join drgs.mr_ipinfo ip  on diag.pmainindex = ip.pmainindex   \n ");
			sql.append("  where to_char(ip.p00290000,'yyyy-mm') between  '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"' and diag.pdtype = 'O' \n");
			sql.append("  group by diag.pmainindex) diaginfo on ip.pmainindex = diaginfo.pmainindex \n");
			sql.append("  LEFT JOIN (SELECT OPER.PMAININDEX, LISTAGG('('||OPER.PO0101||')'||OPER.PO0100,'|')  WITHIN GROUP (ORDER BY OPER.POINDEX)  OPERINFO \n");
			sql.append("  FROM DRGS.MR_OPERATION OPER LEFT JOIN DRGS.MR_IPINFO IP  ON OPER.PMAININDEX = IP.PMAININDEX \n");
			sql.append("  WHERE TO_CHAR(IP.P00290000,'YYYY-MM') BETWEEN  '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"' \n");
			sql.append("  GROUP BY OPER.PMAININDEX) OPERINFO ON IP.PMAININDEX = OPERINFO.PMAININDEX \n");
			sql.append("  left join drgs.t_drgs drg \n ");
			sql.append("  on pd.pardrg_code = drg.drg_code and pd.ver_code = drg.ver_code \n ");
			sql.append("  left join DRGs.t_Adrg adrg \n ");
			sql.append("  on adrg.adrg_code =drg.adrg_code and drg.ver_code = adrg.ver_code \n ");
			sql.append("  left join DRGs.t_Mdcgroup mdc \n ");
			sql.append("  on mdc.mdc_code =adrg.mdc_code and mdc.ver_code = adrg.ver_code \n ");
			sql.append("  left join drgs.t_department dep  \n ");
			sql.append("  on ip.P00300001 = dep.dep_code  \n ");
			sql.append("  left join drgs.t_depgroup dg  \n ");
			sql.append("  on dg.depgroup_code = dep.pardepgroup_code  \n ");
			sql.append("   left join DRGs.t_drgversion dr \n " );
			sql.append("   on pd.ver_code=dr.ver_code   \n ");
			sql.append("  left join drgs.t_annualstd std \n ");
			sql.append("  on pd.pardrg_code = std.as_drg_code and std.as_id = '"+stdId+"' \n ");
			sql.append("  where to_char(ip.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"'  \n ");
			sql.append("  and instr('"+hospCode+"',base.p00010001)>0 \n ");
			sql.append(condition);
			sql.append("  and pd.pardrg_code is not null and ip.P00320000 < 61   \n ");
			sql.append("   and pd.pardrg_code  not in ( \n ");
			sql.append("   select distinct(b.pardrg_code) from DRGs.mr_ipinfo ip   \n ");
			sql.append("   left join drgs.mr_baseinfo base on ip.pmainindex = base.pmainindex  \n ");
			sql.append("   left join DRGs.t_patientdrg b \n ");   
			sql.append("   on ip.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"'\n ");  
			sql.append("   left join DRGs.t_department dep \n ");  
			sql.append("   on ip.P00300001=dep.dep_code \n ");  
			sql.append("   where to_char(ip.P00290000,'yyyy-mm') between '"+dateFrom.substring(0,7)+"' and '"+dateTo.substring(0,7)+"' \n ");
			sql.append("   and instr('"+hospCode+"',base.p00010001)>0 \n ");
			sql.append("   and b.pardrg_code is not null \n ");
			sql.append( condition );
			sql.append("   ) \n ");
		}
		
		//仅本期覆盖
		if("onlyThisPeriod".equals(monitorType)){
			sql.setLength(0);
			//P00050000-病案号 P00060000-姓名 P00070001-性别编码 P00090000-年龄 P00250000-入院日期 P00290000-出院日期
			//P00320000-住院天数 P00800000-总费用 PD0100-诊断描述 PO0100-手术描述
			sql.append("  select ip.pmainindex id,BASE.P00040000 IP_COUNT,base.P00050000 mrNo,base.P00060000 patientName,base.P00070001 sex,base.P00090000 ageYear,TO_CHAR(IP.P00250000, 'YYYY-MM-DD') inDate,TO_CHAR(IP.P00290000, 'YYYY-MM-DD') outDate, \n ");
			sql.append("  dep.dep_name outDep,ip.P00320000 inDays,ip.P00800000 costamount,expsum.drug_cost drugcost ,'('||diag.PD0101||')'||diag.PD0100 diagnoseDessmain,pd.pardrg_code drgcode, \n ");
			sql.append("  drg.drg_desc drg,std.as_weight weight,std.as_avg_days avgDaysStd,std.as_avg_fee avgFeeStd, operinfo.operinfo operation ,diaginfo.diaginfo otherdiag \n ");
			sql.append("  from drgs.mr_ipinfo ip left join drgs.t_patientdrg pd  \n ");
			sql.append("  on ip.pmainindex = pd.parid  and  pd.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join drgs.mr_baseinfo base on ip.pmainindex = base.pmainindex  \n ");
			sql.append("  left join drgs.mr_expense_sum expsum on ip.pmainindex = expsum.pmainindex \n ");
			sql.append("  left join drgs.mr_diagnose diag \n ");
			sql.append("  on ip.pmainindex=diag.pmainindex  and diag.PDType='M' \n ");
			sql.append("  left join (select diag.pmainindex, listagg('('||diag.PD0101||')'||diag.PD0100,'|')  within group (order by diag.pdindex)  diaginfo \n ");
			sql.append("  from drgs.mr_diagnose diag left join drgs.mr_ipinfo ip  on diag.pmainindex = ip.pmainindex   \n ");
			sql.append("  where to_char(ip.p00290000,'yyyy-mm') between  '"+dateFrom+"' and '"+dateTo+"' and diag.pdtype = 'O' \n");
			sql.append("  group by diag.pmainindex) diaginfo on ip.pmainindex = diaginfo.pmainindex \n");
			sql.append("  LEFT JOIN (SELECT OPER.PMAININDEX, LISTAGG('('||OPER.PO0101||')'||OPER.PO0100,'|')  WITHIN GROUP (ORDER BY OPER.POINDEX)  OPERINFO \n");
			sql.append("  FROM DRGS.MR_OPERATION OPER LEFT JOIN DRGS.MR_IPINFO IP  ON OPER.PMAININDEX = IP.PMAININDEX \n");
			sql.append("  WHERE TO_CHAR(IP.P00290000,'YYYY-MM') BETWEEN  '"+dateFrom+"' AND '"+dateTo+"' \n");
			sql.append("  GROUP BY OPER.PMAININDEX) OPERINFO ON IP.PMAININDEX = OPERINFO.PMAININDEX \n");
			sql.append("  left join drgs.t_drgs drg \n ");
			sql.append("  on pd.pardrg_code = drg.drg_code and pd.ver_code = drg.ver_code \n ");
			sql.append("  left join DRGs.t_Adrg adrg \n ");
			sql.append("  on adrg.adrg_code =drg.adrg_code and drg.ver_code = adrg.ver_code \n ");
			sql.append("  left join DRGs.t_Mdcgroup mdc \n ");
			sql.append("  on mdc.mdc_code =adrg.mdc_code and mdc.ver_code = adrg.ver_code \n ");
			sql.append("  left join drgs.t_department dep  \n ");
			sql.append("  on ip.P00300001 = dep.dep_code  \n ");
			sql.append("  left join drgs.t_depgroup dg  \n ");
			sql.append("  on dg.depgroup_code = dep.pardepgroup_code  \n ");
			sql.append("   left join DRGs.t_drgversion dr \n " );
			sql.append("   on pd.ver_code=dr.ver_code   \n ");
			sql.append("  left join drgs.t_annualstd std \n ");
			sql.append("  on pd.pardrg_code = std.as_drg_code and std.as_id = '"+stdId+"' \n ");
			sql.append("  where to_char(ip.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
			sql.append("  and instr('"+hospCode+"',base.p00010001)>0 \n ");
			sql.append(condition);
			sql.append("  and pd.pardrg_code is not null and ip.P00320000 < 61   \n ");
			sql.append("   and pd.pardrg_code  not in ( \n ");
			sql.append("   select distinct(b.pardrg_code) from DRGs.mr_ipinfo ip   \n ");
			sql.append("   left join drgs.mr_baseinfo base on ip.pmainindex = base.pmainindex  \n ");
			sql.append("   left join DRGs.t_patientdrg b \n ");   
			sql.append("   on ip.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"' \n ");  
			sql.append("   left join DRGs.t_department dep \n ");  
			sql.append("   on ip.P00300001=dep.dep_code \n ");  
			sql.append("   where to_char(ip.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom.substring(0,2)+"' and '"+(thisYear-1)+"-"+monthTo.substring(0,2)+"' \n ");
			sql.append("   and instr('"+hospCode+"',base.p00010001)>0 \n ");
			sql.append("   and b.pardrg_code is not null \n ");
			sql.append( condition );
			sql.append("   ) \n ");
		}
		
		//本期小
		if("thisPeriodLittle".equals(monitorType)){
			sql.setLength(0);
			//P00050000-病案号 P00060000-姓名 P00070001-性别编码 P00090000-年龄 P00250000-入院日期 P00290000-出院日期
			//P00320000-住院天数 P00800000-总费用 PD0100-诊断描述 PO0100-手术描述
			sql.append("  select ip.pmainindex id,BASE.P00040000 IP_COUNT,base.P00050000 mrNo,base.P00060000 patientName,base.P00070001 sex,base.P00090000 ageYear,TO_CHAR(IP.P00250000, 'YYYY-MM-DD') inDate,TO_CHAR(IP.P00290000, 'YYYY-MM-DD') outDate, \n ");
			sql.append("  dep.dep_name outDep,ip.P00320000 inDays,ip.P00800000 costamount,expsum.drug_cost drugcost ,'('||diag.PD0101||')'||diag.PD0100 diagnoseDessmain,pd.pardrg_code drgcode, \n ");
			sql.append("  drg.drg_desc drg,std.as_weight weight,std.as_avg_days avgDaysStd,std.as_avg_fee avgFeeStd, operinfo.operinfo operation ,diaginfo.diaginfo otherdiag \n ");
			sql.append("  from drgs.mr_ipinfo ip left join drgs.t_patientdrg pd  \n ");
			sql.append("  on ip.pmainindex = pd.parid  and  pd.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join drgs.mr_baseinfo base on ip.pmainindex = base.pmainindex  \n ");
			sql.append("  left join drgs.mr_expense_sum expsum on ip.pmainindex = expsum.pmainindex \n ");
			sql.append("  left join drgs.mr_diagnose diag \n ");
			sql.append("  on ip.pmainindex=diag.pmainindex  and diag.PDType='M' \n ");
			sql.append("  left join (select diag.pmainindex, listagg('('||diag.PD0101||')'||diag.PD0100,'|')  within group (order by diag.pdindex)  diaginfo \n ");
			sql.append("  from drgs.mr_diagnose diag left join drgs.mr_ipinfo ip  on diag.pmainindex = ip.pmainindex   \n ");
			sql.append("  where to_char(ip.p00290000,'yyyy-mm') between  '"+dateFrom+"' and '"+dateTo+"' and diag.pdtype = 'O' \n");
			sql.append("  group by diag.pmainindex) diaginfo on ip.pmainindex = diaginfo.pmainindex \n");
			sql.append("  LEFT JOIN (SELECT OPER.PMAININDEX, LISTAGG('('||OPER.PO0101||')'||OPER.PO0100,'|')  WITHIN GROUP (ORDER BY OPER.POINDEX)  OPERINFO \n");
			sql.append("  FROM DRGS.MR_OPERATION OPER LEFT JOIN DRGS.MR_IPINFO IP  ON OPER.PMAININDEX = IP.PMAININDEX \n");
			sql.append("  WHERE TO_CHAR(IP.P00290000,'YYYY-MM') BETWEEN  '"+dateFrom+"' AND '"+dateTo+"' \n");
			sql.append("  GROUP BY OPER.PMAININDEX) OPERINFO ON IP.PMAININDEX = OPERINFO.PMAININDEX \n");
			sql.append("  left join drgs.t_drgs drg \n ");
			sql.append("  on pd.pardrg_code = drg.drg_code and pd.ver_code = drg.ver_code \n ");
			sql.append("  left join DRGs.t_Adrg adrg \n ");
			sql.append("  on adrg.adrg_code =drg.adrg_code and drg.ver_code = adrg.ver_code \n ");
			sql.append("  left join DRGs.t_Mdcgroup mdc \n ");
			sql.append("  on mdc.mdc_code =adrg.mdc_code and mdc.ver_code = adrg.ver_code \n ");
			sql.append("  left join drgs.t_department dep  \n ");
			sql.append("  on ip.P00300001 = dep.dep_code  \n ");
			sql.append("  left join drgs.t_depgroup dg  \n ");
			sql.append("  on dg.depgroup_code = dep.pardepgroup_code  \n ");
			sql.append("   left join DRGs.t_drgversion dr \n " );
			sql.append("   on pd.ver_code=dr.ver_code   \n ");
			sql.append("  left join drgs.t_annualstd std \n ");
			sql.append("  on pd.pardrg_code = std.as_drg_code and std.as_id = '"+stdId+"' \n ");
			sql.append("  where to_char(ip.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
			sql.append("  and instr('"+hospCode+"',base.p00010001)>0 \n ");
			sql.append(condition);
			sql.append("  and pd.pardrg_code is not null and ip.P00320000 < 61   \n ");
			sql.append("   and pd.pardrg_code in ( \n ");
			sql.append("   select a.pardrg_code from (  \n ");
			sql.append("   select b.pardrg_code,  \n ");
			sql.append("   avg(P00800000) avgfee  \n ");
			sql.append("   from DRGs.mr_ipinfo ip   \n ");
			sql.append("  left join drgs.mr_baseinfo base on ip.pmainindex = base.pmainindex  \n ");
			sql.append("   left join DRGs.t_patientdrg b    \n ");
			sql.append("   on ip.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("   left join DRGs.t_department dep   \n ");
			sql.append("   on ip.P00300001=dep.dep_code   \n ");
			sql.append("   left join DRGs.t_drgversion dr \n " );
			sql.append("   on b.ver_code=dr.ver_code   \n ");
			sql.append("   left join DRGs.t_annualstd c   \n ");
			sql.append("   on b.pardrg_code=c.as_drg_code and c.as_id = '"+stdId+"'   \n ");
			sql.append("   where to_char(ip.P00290000,'yyyy-mm') between '"+dateFrom.substring(0,7)+"' and '"+dateTo.substring(0,7)+"'  \n ");
			sql.append("  and instr('"+hospCode+"',base.p00010001)>0 \n ");
			sql.append("   and b.pardrg_code is not null \n ");
			sql.append( condition );
			sql.append("   group by b.pardrg_code) a  \n ");
			sql.append("   left join   \n ");
			sql.append("   (  \n ");
			sql.append("   select b.pardrg_code,  \n ");
			sql.append("   avg(P00800000) avgfee  \n ");
			sql.append("   from DRGs.mr_ipinfo ip   \n ");
			sql.append("  left join drgs.mr_baseinfo base on ip.pmainindex = base.pmainindex  \n ");
			sql.append("   left join DRGs.t_patientdrg b    \n ");
			sql.append("   on ip.pmainindex=b.parid and  b.ver_region='"+groupScheme+"'  \n ");
			sql.append("   left join DRGs.t_department dep   \n ");
			sql.append("   on ip.P00300001=dep.dep_code  \n ");
			sql.append("   where to_char(ip.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom.substring(0,2)+"' and '"+(thisYear-1)+"-"+monthTo.substring(0,2)+"'  \n ");
			sql.append("  and instr('"+hospCode+"',base.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("   group by b.pardrg_code  \n ");
			sql.append("   ) b   \n ");
			sql.append("   on a.pardrg_code= b.pardrg_code  \n ");
			sql.append("   where a.avgfee<= b.avgfee   \n ");
			sql.append("   ) \n ");
		}
		
		//本期大
		if("thisPeriodLarge".equals(monitorType)){
			sql.setLength(0);
			//P00050000-病案号 P00060000-姓名 P00070001-性别编码 P00090000-年龄 P00250000-入院日期 P00290000-出院日期
			//P00320000-住院天数 P00800000-总费用 PD0100-诊断描述 PO0100-手术描述
			sql.append("  select ip.pmainindex id,BASE.P00040000 IP_COUNT,base.P00050000 mrNo,base.P00060000 patientName,base.P00070001 sex,base.P00090000 ageYear,TO_CHAR(IP.P00250000, 'YYYY-MM-DD') inDate,TO_CHAR(IP.P00290000, 'YYYY-MM-DD') outDate, \n ");
			sql.append("  dep.dep_name outDep,ip.P00320000 inDays,ip.P00800000 costamount,expsum.drug_cost drugcost ,'('||diag.PD0101||')'||diag.PD0100 diagnoseDessmain,pd.pardrg_code drgcode, \n ");
			sql.append("  drg.drg_desc drg,std.as_weight weight,std.as_avg_days avgDaysStd,std.as_avg_fee avgFeeStd, operinfo.operinfo operation ,diaginfo.diaginfo otherdiag \n ");
			sql.append("  from drgs.mr_ipinfo ip left join drgs.t_patientdrg pd  \n ");
			sql.append("  on ip.pmainindex = pd.parid and  pd.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join drgs.mr_baseinfo base on ip.pmainindex = base.pmainindex  \n ");
			sql.append("  left join drgs.mr_expense_sum expsum on ip.pmainindex = expsum.pmainindex \n ");
			sql.append("  left join drgs.mr_diagnose diag \n ");
			sql.append("  on ip.pmainindex=diag.pmainindex  and diag.PDType='M' \n ");
			sql.append("  left join (select diag.pmainindex, listagg('('||diag.PD0101||')'||diag.PD0100,'|')  within group (order by diag.pdindex)  diaginfo \n ");
			sql.append("  from drgs.mr_diagnose diag left join drgs.mr_ipinfo ip  on diag.pmainindex = ip.pmainindex   \n ");
			sql.append("  where to_char(ip.p00290000,'yyyy-mm') between  '"+dateFrom+"' and '"+dateTo+"' and diag.pdtype = 'O' \n");
			sql.append("  group by diag.pmainindex) diaginfo on ip.pmainindex = diaginfo.pmainindex \n");
			sql.append("  LEFT JOIN (SELECT OPER.PMAININDEX, LISTAGG('('||OPER.PO0101||')'||OPER.PO0100,'|')  WITHIN GROUP (ORDER BY OPER.POINDEX)  OPERINFO \n");
			sql.append("  FROM DRGS.MR_OPERATION OPER LEFT JOIN DRGS.MR_IPINFO IP  ON OPER.PMAININDEX = IP.PMAININDEX \n");
			sql.append("  WHERE TO_CHAR(IP.P00290000,'YYYY-MM') BETWEEN  '"+dateFrom+"' AND '"+dateTo+"' \n");
			sql.append("  GROUP BY OPER.PMAININDEX) OPERINFO ON IP.PMAININDEX = OPERINFO.PMAININDEX \n");
			sql.append("  left join drgs.t_drgs drg \n ");
			sql.append("  on pd.pardrg_code = drg.drg_code and pd.ver_code = drg.ver_code \n ");
			sql.append("  left join DRGs.t_Adrg adrg \n ");
			sql.append("  on adrg.adrg_code =drg.adrg_code and drg.ver_code = adrg.ver_code \n ");
			sql.append("  left join DRGs.t_Mdcgroup mdc \n ");
			sql.append("  on mdc.mdc_code =adrg.mdc_code and mdc.ver_code = adrg.ver_code \n ");
			sql.append("  left join drgs.t_department dep  \n ");
			sql.append("  on ip.P00300001 = dep.dep_code  \n ");
			sql.append("  left join drgs.t_depgroup dg  \n ");
			sql.append("  on dg.depgroup_code = dep.pardepgroup_code  \n ");
			sql.append("   left join DRGs.t_drgversion dr \n " );
			sql.append("   on pd.ver_code=dr.ver_code   \n ");
			sql.append("  left join drgs.t_annualstd std \n ");
			sql.append("  on pd.pardrg_code = std.as_drg_code and std.as_id = '"+stdId+"' \n ");
			sql.append("  where to_char(ip.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
			sql.append("  and instr('"+hospCode+"',base.p00010001)>0 \n ");
			sql.append(condition);
			sql.append("  and pd.pardrg_code is not null and ip.P00320000 < 61   \n ");
			sql.append("   and pd.pardrg_code in ( \n ");
			sql.append("   select a.pardrg_code from (  \n ");
			sql.append("   select b.pardrg_code,  \n ");
			sql.append("   avg(P00800000) avgfee  \n ");
			sql.append("   from DRGs.mr_ipinfo ip   \n ");
			sql.append("  left join drgs.mr_baseinfo base on ip.pmainindex = base.pmainindex  \n ");
			sql.append("   left join DRGs.t_patientdrg b    \n ");
			sql.append("   on ip.pmainindex=b.parid and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("   left join DRGs.t_department dep   \n ");
			sql.append("   on ip.P00300001=dep.dep_code   \n ");
			sql.append("   left join DRGs.t_drgversion dr \n " );
			sql.append("   on b.ver_code=dr.ver_code   \n ");
			sql.append("   left join DRGs.t_annualstd c   \n ");
			sql.append("   on b.pardrg_code=c.as_drg_code and c.as_id = '"+stdId+"'   \n ");
			sql.append("   where to_char(ip.P00290000,'yyyy-mm') between '"+dateFrom.substring(0,7)+"' and '"+dateTo.substring(0,7)+"'  \n ");
			sql.append("  and instr('"+hospCode+"',base.p00010001)>0 \n ");
			sql.append("   and b.pardrg_code is not null \n ");
			sql.append( condition );
			sql.append("   group by b.pardrg_code) a  \n ");
			sql.append("   left join   \n ");
			sql.append("   (  \n ");
			sql.append("   select b.pardrg_code,  \n ");
			sql.append("   avg(P00800000) avgfee  \n ");
			sql.append("   from DRGs.mr_ipinfo ip   \n ");
			sql.append("  left join drgs.mr_baseinfo base on ip.pmainindex = base.pmainindex  \n ");
			sql.append("   left join DRGs.t_patientdrg b    \n ");
			sql.append("   on ip.pmainindex=b.parid and  b.ver_region='"+groupScheme+"'  \n ");
			sql.append("   left join DRGs.t_department dep   \n ");
			sql.append("   on ip.P00300001=dep.dep_code  \n ");
			sql.append("   where to_char(ip.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom.substring(0,2)+"' and '"+(thisYear-1)+"-"+monthTo.substring(0,2)+"'  \n ");
			sql.append("  and instr('"+hospCode+"',base.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("   group by b.pardrg_code  \n ");
			sql.append("   ) b   \n ");
			sql.append("   on a.pardrg_code= b.pardrg_code  \n ");
			sql.append("   where a.avgfee > b.avgfee   \n ");
			sql.append("   ) \n ");
		}
		List<Map<String,Object>> caseList = jdbcTemplateObj.queryForList(sql.toString()) ;
		return caseList;
	}
    
	/**
	 *  根据drgCode和年份查询主诊组信息
	 *  @Author QiaoYiLong
	 *  @param drgsAnalysis
	 *  @return 
	 *  @Date:2018年9月4日下午3:30:55
	 */
	@Override
	@Cacheable(value = "drgsCaseDetail3.1.1", key = "'getClinicGroupByDrgCode' + #drgsAnalysis.drgCode + #drgsAnalysis.wardCode + #drgsAnalysis.depCode + #drgsAnalysis.depGroupCode + #drgsAnalysis.hospCode "
			+" + #drgsAnalysis.stdId + #drgsAnalysis.verRegion + #drgsAnalysis.dateFrom + #drgsAnalysis.dateTo + #drgsAnalysis.yearFromA ")
	public List<Map<String,Object>> getClinicGroupByDrgCode(DrgsAnalysis drgsAnalysis) {
		String wardCode = drgsAnalysis.getWardCode();
		String depCode = drgsAnalysis.getDepCode();
		String depGroupCode = drgsAnalysis.getDepGroupCode();
		String hospCode = drgsAnalysis.getHospCode();
		String drgCode = drgsAnalysis.getDrgCode();//
		String dateFromThis = DrgsIndex.formateDateFrom(drgsAnalysis.getYearFromA()).substring(0,7);
		String groupScheme = drgsAnalysis.getVerRegion();
		String stdId = drgsAnalysis.getStdId();
		String monitorType = drgsAnalysis.getMonitorType();//这里存是否费用超标
		String condition = "";//条件字符串
		String conditionOver = ""; //费用超标条件字符串
		List<Map<String,Object>>  result = new ArrayList<Map<String,Object>>();
		if ( null!=groupScheme && StringUtils.hasLength(groupScheme) ){
			groupScheme =  " and  pdrg.ver_region='"+groupScheme+"' ";
		}
		//科室组
		if (StringUtils.hasLength(depGroupCode)&&!"null".equals(depGroupCode)&&!"hosp".equals(depGroupCode)) {
			condition += "  and dep.pardepgroup_code = '"+depGroupCode+"'";
		} 
		//科室
		if (StringUtils.hasLength(depCode)&&!"null".equals(depCode)) {
			if(depCode.indexOf(",")>-1)
			{//多个科室
				depCode = "'"+depCode.replaceAll(",","','")+"'";
				condition += "  and dep.dep_code in ("+depCode+")";
			}
			else
			{//一个科室
				condition += "  and dep.dep_code = '"+depCode+"'";
			}
		}
		//病区
		if (StringUtils.hasLength(wardCode)&&!"null".equals(wardCode)) {
			condition += "  and ip.P00310001 = '"+wardCode+"'";
		}
		//是否费用超标
		if (StringUtils.hasLength(monitorType) && "overproof".equals(monitorType)) {
			conditionOver = " where 1=1 and nvl(a.avgFee,0)-nvl(c.as_avg_fee,0)>0 \n ";
		}
		StringBuilder sql  = new StringBuilder();
		//主诊组信息
		sql.setLength(0);
		sql.append("   select a.groupCode,a.group_name ,a.patientNum ,a.avgFee ,a.avgDays,a.pardrg_code,c.as_avg_fee,c.as_avg_days   \n ");
		sql.append("   from ( select ip.P00601001 groupCode,doc.group_name ,pdrg.pardrg_code,count(1) patientNum ,round(avg(ip.P00800000),2) avgFee ,round(avg(ip.P00320000),2) avgDays  \n ");
		sql.append("   from DRGs.mr_ipinfo ip  \n ");
		sql.append("   left join DRGs.mr_baseinfo bs on ip.pmainindex=bs.pmainindex  \n ");
		sql.append("   left join DRGs.t_patientdrg pdrg on ip.pmainindex=pdrg.parid "+groupScheme+"   \n ");
		sql.append("   left join DRGs.t_department dep on ip.P00300001=dep.dep_code  \n ");
		sql.append("   left join DRGs.t_clinicgroup doc  on ip.P00601001=doc.group_code  \n ");
		sql.append("   where to_char(ip.P00290000,'yyyy-mm') between '"+dateFromThis.substring(0,7)+"' and '"+dateFromThis.substring(0,7)+"' \n ");
		sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append("   and pdrg.pardrg_code='"+drgCode+"' \n ");
		sql.append( condition );
		sql.append("   group by ip.P00601001,doc.group_name,pdrg.pardrg_code \n ");
		sql.append("   ) a  left join DRGs.t_annualstd c  \n ");
		sql.append("   on a.pardrg_code=c.as_drg_code and c.as_id='"+stdId+"'  \n ");
		sql.append( conditionOver );
		result = jdbcTemplateObj.queryForList(sql.toString()) ;
		return result;
	} 
	
	/**
	 *  根据drgCode，年份，asId 查询t_annualstd的 年度标杆值
	 *  @Author QiaoYiLong
	 *  @param drgsAnalysis
	 *  @return 
	 *  @Date:2018年9月5日下午4:41:46
	 */
	@Override
	@Cacheable(value = "drgsCaseDetail3.1.1", key = "'getAnnualStdByDrgCode' + #drgsAnalysis.stdId + #drgsAnalysis.drgCode + #drgsAnalysis.dateFrom ")
	public List<Map<String,Object>> getAnnualStdByDrgCode(DrgsAnalysis drgsAnalysis) {
		String drgCode = drgsAnalysis.getDrgCode();//
		String dateFrom = DrgsIndex.formateDateFrom(drgsAnalysis.getDateFrom()).substring(0,7);
		String stdId = drgsAnalysis.getStdId();//标杆的id
		List<Map<String,Object>>  result = new ArrayList<Map<String,Object>>();
		if ( !(null!=dateFrom && dateFrom.length() >= 4 && StringUtils.hasLength(drgCode)) ){
			return result;
		}
    	//如果标杆id为空则返回
    	if( !StringUtils.hasLength(stdId) || null == stdId || "".equals(stdId) || "null".equals(stdId) ){
    		return result ;
    	}	
		StringBuilder sql  = new StringBuilder();
		//主诊组信息
		sql.setLength(0);
		sql.append("   select c.*  \n ");
		sql.append("   from DRGs.t_annualstd c  \n ");
		sql.append("   where c.as_drg_code='"+drgCode+"'  \n ");
		sql.append("   and c.as_id = '"+stdId+"'  \n ");
		result = jdbcTemplateObj.queryForList(sql.toString()) ;
		return result;
	}
	
	/** 查询指定主诊组id的病例详情
	 *  @Author QiaoYiLong
	 *  @param drgsAnalysis (drgsAnalysis.drgCode必填，drgsAnalysis.monitorType存主诊组id)
	 *  @return 
	 *  @Date:2018年9月6日上午11:25:30
	 */
	@Override
	@Cacheable(value = "drgsCaseDetail3.1.1", key = "'loadClinicCase' + #drgsAnalysis.wardCode + #drgsAnalysis.depCode + #drgsAnalysis.depGroupCode  + #drgsAnalysis.hospCode "
			+ " + #drgsAnalysis.stdId + #drgsAnalysis.verRegion + #drgsAnalysis.dateFrom + #drgsAnalysis.drgCode + #drgsAnalysis.monitorType")
	public List<Map<String,Object>> loadClinicCase(DrgsAnalysis drgsAnalysis) {
		String wardCode = drgsAnalysis.getWardCode();
		String depCode = drgsAnalysis.getDepCode();
		String depGroupCode = drgsAnalysis.getDepGroupCode();
		String hospCode = drgsAnalysis.getHospCode();
		String dateFrom = DrgsIndex.formateDateFrom(drgsAnalysis.getDateFrom()).substring(0,7);
		String dateTo = DrgsIndex.formateDateTo( drgsAnalysis.getDateTo()).substring(0,7);
		String monitorType = drgsAnalysis.getMonitorType();//这里存主诊组id
		String stdId= drgsAnalysis.getStdId();//标杆的id
		String drgCode = drgsAnalysis.getDrgCode();
		String groupScheme = drgsAnalysis.getVerRegion();
    	String condition = "";//条件字符串
    	
		List<Map<String,Object>>  result = new ArrayList<Map<String,Object>>();
		if ( !(StringUtils.hasLength(stdId) && StringUtils.hasLength(drgCode) && StringUtils.hasLength(monitorType)) ){
			return result;
		}
		//科室组
		if (StringUtils.hasLength(depGroupCode)&&!"null".equals(depGroupCode)&&!"hosp".equals(depGroupCode)) {
			condition += " and dep.pardepgroup_code = '"+depGroupCode+"'";
		} 
		//科室
		if (StringUtils.hasLength(depCode)&&!"null".equals(depCode)) {
			if(depCode.indexOf(",")>-1)
			{//多个科室
				depCode = "'"+depCode.replaceAll(",","','")+"'";
				condition += " and dep.dep_code in ("+depCode+")";
			}
			else
			{//一个科室
				condition += " and dep.dep_code = '"+depCode+"'";
			}
		}
		//病区
		if (StringUtils.hasLength(wardCode)&&!"null".equals(wardCode)) {
			condition += " and ip.P00310001 = '"+wardCode+"'";
		}
		StringBuilder sql  = new StringBuilder();

		sql.setLength(0);
		//P00050000-病案号 P00060000-姓名 P00070001-性别编码 P00090000-年龄 P00250000-入院日期 P00290000-出院日期
		//P00320000-住院天数 P00800000-总费用 PD0100-诊断描述 PO0100-手术描述
		sql.append("  select ip.pmainindex id,base.P00050000 mrNo,base.P00060000 patientName,base.P00070001 sex,base.P00090000 ageYear,TO_CHAR(IP.P00250000, 'YYYY-MM-DD') inDate,TO_CHAR(IP.P00290000, 'YYYY-MM-DD') outDate, \n ");
		sql.append("  dep.dep_name outDep,ip.P00320000 inDays,ip.P00800000 costamount,expsum.drug_cost drugcost,'('||diag.PD0101||')'||diag.PD0100 diagnoseDessmain,pd.pardrg_code drgcode, \n ");
		sql.append("  drg.drg_desc drg,std.as_weight weight,std.as_avg_days avgDaysStd,std.as_avg_fee avgFeeStd, operinfo.operinfo operation ,diaginfo.diaginfo otherdiag \n ");
		sql.append("  from drgs.mr_ipinfo ip left join drgs.t_patientdrg pd  \n ");
		sql.append("  on ip.pmainindex = pd.parid  and  pd.ver_region='"+groupScheme+"'\n ");
		sql.append("  left join drgs.mr_baseinfo base on ip.pmainindex = base.pmainindex  \n ");
		sql.append("  left join drgs.mr_expense_sum expsum on ip.pmainindex = expsum.pmainindex \n ");
		sql.append("  left join drgs.mr_diagnose diag \n ");
		sql.append("   on ip.pmainindex=diag.pmainindex  and diag.PDType='M' \n ");
		sql.append("  left join (select diag.pmainindex, listagg('('||diag.PD0101||')'||diag.PD0100,'|')  within group (order by diag.pdindex)  diaginfo \n ");
		sql.append("  from drgs.mr_diagnose diag left join drgs.mr_ipinfo ip  on diag.pmainindex = ip.pmainindex   \n ");
		sql.append("  where to_char(ip.p00290000,'yyyy-mm') between  '"+dateFrom+"' and '"+dateTo+"' and diag.pdtype = 'O' \n");
		sql.append("  group by diag.pmainindex) diaginfo on ip.pmainindex = diaginfo.pmainindex \n");
		sql.append("  LEFT JOIN (SELECT OPER.PMAININDEX, LISTAGG('('||OPER.PO0101||')'||OPER.PO0100,'|')  WITHIN GROUP (ORDER BY OPER.POINDEX)  OPERINFO \n");
		sql.append("  FROM DRGS.MR_OPERATION OPER LEFT JOIN DRGS.MR_IPINFO IP  ON OPER.PMAININDEX = IP.PMAININDEX \n");
		sql.append("  WHERE TO_CHAR(IP.P00290000,'YYYY-MM') BETWEEN  '"+dateFrom+"' AND '"+dateTo+"' \n");
		sql.append("  GROUP BY OPER.PMAININDEX) OPERINFO ON IP.PMAININDEX = OPERINFO.PMAININDEX \n");
		sql.append("  left join drgs.t_drgs drg \n ");
		sql.append("  on pd.pardrg_code = drg.drg_code and pd.ver_code = drg.ver_code \n ");
		sql.append("  left join DRGs.t_Adrg adrg \n ");
		sql.append("  on adrg.adrg_code =drg.adrg_code and drg.ver_code = adrg.ver_code \n ");
		sql.append("  left join DRGs.t_Mdcgroup mdc \n ");
		sql.append("  on mdc.mdc_code =adrg.mdc_code and mdc.ver_code = adrg.ver_code \n ");
		sql.append("  left join drgs.t_department dep  \n ");
		sql.append("  on ip.P00300001 = dep.dep_code  \n ");
		sql.append("  left join drgs.t_depgroup dg  \n ");
		sql.append("  on dg.depgroup_code = dep.pardepgroup_code  \n ");
		sql.append("   left join DRGs.t_drgversion dr \n " );
		sql.append("   on pd.ver_code=dr.ver_code   \n ");
		sql.append("  left join drgs.t_annualstd std \n ");
		sql.append("  on pd.pardrg_code = std.as_drg_code and std.as_id = '"+stdId+"' \n ");
		sql.append("  where to_char(ip.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
		sql.append("  and instr('"+hospCode+"',base.p00010001)>0 \n ");
		sql.append(condition);
		sql.append("  and pd.pardrg_code = '"+drgCode+"' and ip.P00320000 < 61   \n ");
		sql.append("  and ip.P00601001 = '"+monitorType+"'    \n ");
		result = jdbcTemplateObj.queryForList(sql.toString()) ;
		return result;
	}
}
